XML Querying with SQL Server – The Basics

Joe Moore

Published Jul 12, 2019

SQL Server allows us to output the result set of a query as XML, it also allows us to shred the contents of an XML file into a table.

An XML document is comprised of Tags and these start with < and end with /> so the following is an example of a tag. XML documents are ordered meaning that the position of the elements within the well-formed document matter.

More XML Here

</customers>

It’s worth remembering that XML documents are case-sensitive. XML documents have attributes an attribute is the following as shown in the below example.

</Customers>

In the example shown above an attribute is custid=”1” . When your XML document contains attributes it’s know as attribute-centric presentation. If your XML document does not contain attributes it’s know as element-centric presentation.

Producing XML using Queries

The following examples used the WideWorldImporters Database running on SQL server 2017. When creating XML documents, we use the FOR XML clause the first option is RAW.

SELECT SH.[StockItemID]

,SI.StockItemName

,[QuantityOnHand]

,[BinLocation]

,[LastStocktakeQuantity]

,[LastCostPrice]

,[ReorderLevel]

,[TargetStockLevel]

,[LastEditedWhen]

FROM [WideWorldImporters].[Warehouse].[StockItemHoldings] SH

inner join Warehouse.StockItems as SI

ON SI.StockItemID = SH.StockItemID

for xml raw

This produced the following output

The above output shows that this is an XML fragment because it does not contain a root node. We can change this by including the ROOT(‘NAME’) option as shown.

SELECT SH.[StockItemID]

,SI.StockItemName

,[QuantityOnHand]

,[BinLocation]

,[LastStocktakeQuantity]

,[LastCostPrice]

,[ReorderLevel]

,[TargetStockLevel]

,[LastEditedWhen]

FROM [WideWorldImporters].[Warehouse].[StockItemHoldings] SH

inner join Warehouse.StockItems as SI

ON SI.StockItemID = SH.StockItemID

for xml raw, root('Stock')

This produced the following:

<Stock>

</stock>

To have the XML document returned as element-centric we add ELEMENTS to the options as shown below.

SELECT SH.[StockItemID]

,SI.StockItemName

,[QuantityOnHand]

,[BinLocation]

,[LastStocktakeQuantity]

,[LastCostPrice]

,[ReorderLevel]

,[TargetStockLevel]

,[LastEditedWhen]

FROM [WideWorldImporters].[Warehouse].[StockItemHoldings] SH

inner join Warehouse.StockItems as SI

ON SI.StockItemID = SH.StockItemID

for xml raw, elements,root('Stock')

This produces the following output

<Stock>

<row>

<StockItemName>USB missile launcher (Green)</StockItemName>

</row>

</stock>

XML Namespaces

We can add namespaces to our queries to help to distinguish between elements from different business areas, different companies and departments. The below example shows how to do this, also you can alias namespaces just as you do with tables.

with xmlnamespaces('CustomerOrders' as co)

select [co:customers].custid, [co:customers].contactname, [co:customers].contacttitle, [co:orders].shipcountry, [co:orders].orderdate

from sales.customers as [co:customers]

inner join sales.Orders as [co:orders]

on [co:customers].custid= [co:orders].custid

order by [co:customers].custid

for xml auto, elements, root('Customers')

this produces the following output. (the below is just a snippet)

<co:customers>

<contactname>Allen, Michael</contactname>

<contacttitle>Sales Representative</contacttitle>

<co:orders>