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>
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