Monday, November 11, 2013

XML on SQL Server

We are going to be using Microsoft SQL Server's internal queuing mechanism, Service Broker.  This mechanism can move any kind of data, but it is expecting XML payloads.

There are two technologies that have to be mastered here:  Service Broker and conversion between XML and SQL data.

SQL Server has a set of tools used for this.  I am using the book 'SQL Queries Volume 5:XML Querying Techniques for SQL Server 2012 by Pinal Dave and Rick Morelan.

The first problem is creating well formed XML.

Using the SQL:
select * from location for xml raw('location'), root ('locations')

I can generate the following output:
<locations>
  <location LocationID="1" Street="111 First ST" City="Seattle" State="WA" />
  <location LocationID="2" Street="222 Second AVE" City="Boston" State="MA" />
  <location LocationID="3" Street="333 Third PL" City="Chicago" State="IL" />
  <location LocationID="4" Street="444 Ruby ST" City="Spokane" State="WA" />
</locations>

Here is the magic:
for xml raw creates the output as XML
('location') names each row as a location
root forces the creation of well formed xml, with a root element
('locations') assigns the name of the root element.  Without this, the root would be called root.

Changing the SQL by adding elements on the end:
select * 
from location 
for xml raw('location'), 
    root ('locations'), 
    elements

puts each column into its own XML attribute, instead of into elements on the row:
<locations>
  <location>
    <LocationID>1</LocationID>
    <Street>111 First ST</Street>
    <City>Seattle</City>
    <State>WA</State>
  </location>
  <location>
    <LocationID>2</LocationID>
    <Street>222 Second AVE</Street>
    <City>Boston</City>
    <State>MA</State>
  </location>
  <location>
    <LocationID>3</LocationID>
    <Street>333 Third PL</Street>
    <City>Chicago</City>
    <State>IL</State>
  </location>
  <location>
    <LocationID>4</LocationID>
    <Street>444 Ruby ST</Street>
    <City>Spokane</City>
    <State>WA</State>
  </location>
</locations>

If any elements are null, they will be omitted.  If you want them to be included. you need to add the XSINIL argument to the end of the query.

select * from employee where lastname ='marshbank' for xml raw ('employee'), root ('Employees'), elements xsinil

Notice how LocationID has an attribute named xsi:nil

<Employees xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <employee>
    <EmpID>8</EmpID>
    <LastName>Marshbank</LastName>
    <FirstName>John</FirstName>
    <HireDate>2001-11-15T00:00:00</HireDate>
    <LocationID xsi:nil="true" />
    <ManagerID>4</ManagerID>
    <Status>Active      </Status>
  </employee>
</Employees>

Things start getting more interesting with the AUTO mode:

SELECT City, 
       FirstName, 
       LastName 
  FROM Location 
 INNER JOIN Employee ON Location.LocationID = Employee.LocationID 
 ORDER BY City
 FOR XML AUTO, ROOT

The output is automatically grouped by table source:

<root>
  <Location City="Boston">
    <Employee FirstName="Lee" LastName="Osako" />
    <Employee FirstName="James" LastName="Newton" />
    <Employee FirstName="Terry" LastName="O'Haire" />
  </Location>
  <Location City="Seattle">
    <Employee FirstName="Alex" LastName="Adams" />
    <Employee FirstName="Barry" LastName="Brown" />
    <Employee FirstName="David" LastName="Kennson" />
    <Employee FirstName="Eric" LastName="Bender" />
    <Employee FirstName="David" LastName="Lonning" />
    <Employee FirstName="Sally" LastName="Smith" />
    <Employee FirstName="Phil" LastName="Wilconkinski" />
  </Location>
  <Location City="Spokane">
    <Employee FirstName="Lisa" LastName="Kendall" />
    <Employee FirstName="Barbara" LastName="O'Neil" />
  </Location>
</root>

For additional control, we can use the PATH argument.  This gives us the ability to determine whether columns are treated as elements or attributes.  Note how the forces City to be an attribute instead of an element.  We are also renaming columns by using aliases for the column names.

SELECT City AS [@CityName], 
       FirstName AS [First], 
       LastName AS [Last]
  FROM location AS loc 
 INNER JOIN Employee AS emp 
       ON loc.LocationID = emp.LocationID 
   FOR XML PATH('Employee'), 
  ROOT('Employees')

<Employees>
  <Employee CityName="Seattle">
    <First>Alex</First>
    <Last>Adams</Last>
  </Employee>
  <Employee CityName="Seattle">
    <First>Barry</First>
    <Last>Brown</Last>
  </Employee>
  <Employee CityName="Boston">
    <First>Lee</First>
    <Last>Osako</Last>
  </Employee>
  <Employee CityName="Seattle">
    <First>David</First>
    <Last>Kennson</Last>
  </Employee>
  <Employee CityName="Seattle">
    <First>Eric</First>
    <Last>Bender</Last>
  </Employee>
  <Employee CityName="Spokane">
    <First>Lisa</First>
    <Last>Kendall</Last>
  </Employee>
  <Employee CityName="Seattle">
    <First>David</First>
    <Last>Lonning</Last>
  </Employee>
  <Employee CityName="Boston">
    <First>James</First>
    <Last>Newton</Last>
  </Employee>
  <Employee CityName="Boston">
    <First>Terry</First>
    <Last>O'Haire</Last>
  </Employee>
  <Employee CityName="Seattle">
    <First>Sally</First>
    <Last>Smith</Last>
  </Employee>
  <Employee CityName="Spokane">
    <First>Barbara</First>
    <Last>O'Neil</Last>
  </Employee>
  <Employee CityName="Seattle">
    <First>Phil</First>
    <Last>Wilconkinski</Last>
  </Employee>
</Employees>

Finally, we can make artificial parent nodes.  Notice the Name/ at the beginning of the alias for the FirstName and LastName.  This will create an element called Name that will include elements for firstname and lastname.

SELECT City AS [@ CityName], 
       FirstName AS [Name/First], 
       LastName AS [Name/Last] 
  FROM location AS loc 
 INNER JOIN employee AS emp 
       ON loc.LocationID = emp.LocationID 
 WHERE City = 'Boston'
   FOR XML PATH(' Employee'), 
       ROOT(' Employees')

Notice the Name element:

<Employees>
  <Employee CityName="Boston">
    <Name>
      <First>Lee</First>
      <Last>Osako</Last>
    </Name>
  </Employee>
  <Employee CityName="Boston">
    <Name>
      <First>James</First>
      <Last>Newton</Last>
    </Name>
  </Employee>
  <Employee CityName="Boston">
    <Name>
      <First>Terry</First>
      <Last>O'Haire</Last>
    </Name>
  </Employee>
</Employees>

No comments:

Post a Comment