FOR XML RAW in SQL Server

XML has fast become simplest and effective way of data exchange. It has made data transmission easy without worrying about underlying platform. XML has structured format that is universally recognized and offer clean presentation on both receivers as well as sender's end. It has become standard in almost everything. There is no need of any specialized program to read or write XML and application as simple as Notepad can be used to read or write XML. Hierarchal data can be represented easily in XML. Even Android application development involves creating user interface using XML descriptor.

In this article we will go over as how we can retrieve tabular data as XML and some XML basics. We will first go over as how we can retrieve tabular data in XML format. We will use AdventureWorks table for this.

To begin with, XML has 4 modes that we can use to retrieve tabular data in XML format. In this article we will only cover For XML Raw mode and would cover the rest in subsequent articles.

  1. FOR XML Raw
  2. FOR XML Auto
  3. FOR XML Path
  4. FOR XML Explicit

Let's start with a simple query from AdventureWorks and add FOR XML RAW to it

select pc.ProductCategoryID, pc.Name as PCName,pc.ModifiedDate ,p.ProductID ,p.Name ,p.Color ,p.Size

from SalesLT.Product p inner join SalesLT.ProductCategory pc

ON p.ProductCategoryID =pc.ProductCategoryID

where pc.ProductCategoryID<10

FOR XML RAW

It gives result in below format

<row ProductCategoryID="6" PCName="Road Bikes" ModifiedDate="1998-06-01T00:00:00" ProductID="749" Name="Road-150 Red, 62" Color="Red" Size="62" />

<row ProductCategoryID="6" PCName="Road Bikes" ModifiedDate="1998-06-01T00:00:00" ProductID="750" Name="Road-150 Red, 44" Color="Red" Size="44" />

 

Few things to note here is everything we are getting here is under attribute with one element "row" for every row. Also, we cannot have 2 attribute with same name. If we run the above query without giving alias to pc.Name or to p.Name, it will throw an error.

Lets try converting this so that we get elements instead of attribute.

 

select pc.ProductCategoryID, pc.Name as PCName,pc.ModifiedDate ,p.ProductID ,p.Name ,p.Color ,p.Size

from SalesLT.Product p inner join SalesLT.ProductCategory pc

ON p.ProductCategoryID =pc.ProductCategoryID

where pc.ProductID=749

FOR XML RAW, ELEMENTS

<row>

<ProductCategoryID>6</ProductCategoryID>

<PCName>Road Bikes</PCName>

<ModifiedDate>1998-06-01T00:00:00</ModifiedDate>

<ProductID>749</ProductID>

<Name>Road-150 Red, 62</Name>

<Color>Red</Color>

<Size>62</Size>

</row>

We see that just by adding Elemets have converted all attributes to Elements. Tag name row is by default, if we want to change we just have to specify with FOR XML RAW as below:

select pc.ProductCategoryID, pc.Name as PCName,pc.ModifiedDate ,p.ProductID ,p.Name ,p.Color ,p.Size

from SalesLT.Product p inner join SalesLT.ProductCategory pc

ON p.ProductCategoryID =pc.ProductCategoryID

where pc.ProductCategoryID<10

FOR XML RAW('orderDetails'), ELEMENTS

<orderDetails>

<ProductCategoryID>6</ProductCategoryID>

<PCName>Road Bikes</PCName>

<ModifiedDate>1998-06-01T00:00:00</ModifiedDate>

<ProductID>749</ProductID>

<Name>Road-150 Red, 62</Name>

<Color>Red</Color>

<Size>62</Size>

</orderDetails>

….

We can make the above output a little more neater by adding a ROOT element as below

select pc.ProductCategoryID, pc.Name as PCName,pc.ModifiedDate ,p.ProductID ,p.Name ,p.Color ,p.Size

from SalesLT.Product p inner join SalesLT.ProductCategory pc

ON p.ProductCategoryID =pc.ProductCategoryID

where pc.ProductCategoryID=10

FOR XML RAW('orderDetails'), ELEMENTS,ROOT ('order')

 

<order>

<orderDetails>

<ProductCategoryID>10</ProductCategoryID>

<PCName>Brakes</PCName>

<ModifiedDate>1998-06-01T00:00:00</ModifiedDate>

<ProductID>907</ProductID>

<Name>Rear Brakes</Name>

<Color>Silver</Color>

</orderDetails>

<order>

……….

 

It is important to understand as how NULL values are handled, lets see this by tweaking the our query as shown below.

 

select pc.ProductCategoryID, pc.Name as PCName,pc.ModifiedDate

,p.ProductID ,p.Name ,p.Color ,p.Weight

from SalesLT.Product p inner join SalesLT.ProductCategory pc

ON p.ProductCategoryID =pc.ProductCategoryID

where pc.ProductCategoryID in(8,9) and p.ProductID in(994, 947)

FOR XML RAW('orderDetails'), ELEMENTS

<orderDetails>

<ProductCategoryID>8</ProductCategoryID>

<PCName>Handlebars</PCName>

<ModifiedDate>1998-06-01T00:00:00</ModifiedDate>

<ProductID>947</ProductID>

<Name>HL Touring Handlebars</Name>

<Color>Green</Color>

</orderDetails>

<orderDetails>

<ProductCategoryID>9</ProductCategoryID>

<PCName>Bottom Brackets</PCName>

<ModifiedDate>1998-06-01T00:00:00</ModifiedDate>

<ProductID>994</ProductID>

<Name>LL Bottom Bracket</Name>

<Color>Green</Color>

<Weight>223.00</Weight>

</orderDetails>

………

Above we see that ProductID 947 have Weight=NULL and hence the element is completely missing, but if we want to specify null value in XML we would need to add XSINIL in our query as below:

select pc.ProductCategoryID, pc.Name as PCName,pc.ModifiedDate

,p.ProductID ,p.Name ,p.Color ,p.Weight

from SalesLT.Product p inner join SalesLT.ProductCategory pc

ON p.ProductCategoryID =pc.ProductCategoryID

where pc.ProductCategoryID in(8,9) and p.ProductID in(994, 947)

FOR XML RAW('orderDetails'), ELEMENTS XSINIL

<orderDetails xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<ProductCategoryID>8</ProductCategoryID>

<PCName>Handlebars</PCName>

<ModifiedDate>1998-06-01T00:00:00</ModifiedDate>

<ProductID>947</ProductID>

<Name>HL Touring Handlebars</Name>

<Color>Green</Color>

<Weight xsi:nil="true" />

</orderDetails>

<orderDetails xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<ProductCategoryID>9</ProductCategoryID>

<PCName>Bottom Brackets</PCName>

<ModifiedDate>1998-06-01T00:00:00</ModifiedDate>

<ProductID>994</ProductID>

<Name>LL Bottom Bracket</Name>

<Color>Green</Color>

<Weight>223.00</Weight>

</orderDetails>

 

Now there are times when we are sending the feed in XML format, the receiving application may need the schema information. This can be easily done by adding by adding the keyword XMLSchema or XMLData. However, it is important to note that when ROOT element and tag element with RAW is specified e.g. FOR XML RAW('orderDetails'), ELEMENTS, ROOT('order') then XMLDATA is not permitted. Below is an example

select pc.ProductCategoryID, pc.Name as PCName,pc.ModifiedDate ,p.ProductID ,p.Name ,p.Color ,p.Size

from SalesLT.Product p inner join SalesLT.ProductCategory pc

ON p.ProductCategoryID =pc.ProductCategoryID

where pc.ProductCategoryID=10

FOR XML RAW, ELEMENTS, XMLDATA

 

select pc.ProductCategoryID, pc.Name as PCName,pc.ModifiedDate ,p.ProductID ,p.Name ,p.Color ,p.Size

from SalesLT.Product p inner join SalesLT.ProductCategory pc

ON p.ProductCategoryID =pc.ProductCategoryID

where pc.ProductCategoryID=10

FOR XML RAW('orderDetails'), ELEMENTS, XMLSCHEMA ,ROOT ('order')

Suppose you have a table containg a column with Image or varbinary datatypes, FOR XML provides us the capability to generate the XML containing such columns. SQL Server base 64 is responsible for encoding the binary data to character string.

select pc.ProductCategoryID, pc.Name as PCName,pc.ModifiedDate

,p.ProductID ,p.Name ,p.ThumbNailPhoto

from SalesLT.Product p inner join SalesLT.ProductCategory pc

ON p.ProductCategoryID =pc.ProductCategoryID

where pc.ProductCategoryID=10

FOR XML RAW('orderDetails'), ELEMENTS , ROOT ('order')

,Binary Base64

<order>

<orderDetails>

<ProductCategoryID>10</ProductCategoryID>

<PCName>Brakes</PCName>

<ModifiedDate>1998-06-01T00:00:00</ModifiedDate>

<ProductID>907</ProductID>

<Name>Rear Brakes</Name>

<ThumbNailPhoto>R0lGODlhUAAxAPcAAAAAAIAAAACAAICAAAAAgIAAgACAgICAgMDAwP8AAAD/AP//AAAA//8A/wD//////

wAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAMwAAZgAAmQAAzAAA

/wAzAAAzMwAzZgAzmQAzzAAz/wBmAABmMwBmZgBmmQBmzABm/wCZAACZMwCZZgCZmQCZzACZ/wDMAADMMwDMZgDMmQDMzADM/wD/AAD/MwD/ZgD/mQD/zAD//zMAADMAMzMAZjMAmTMAzDMA

/zMzADMzMzMzZjMzmTMzzDMz/zNmADNmMzNmZjNmmTNmzDNm/zOZADOZMzOZZjOZmTOZzDOZ/zPMADPMMzPMZjPMmTPMzDPM/zP/ADP/MzP/ZjP/mTP/zDP//2YAAGYAM2YAZmYAmWYAzGYA

/2YzAGYzM2YzZmYzmWYzzGYz/2ZmAGZmM2ZmZmZmmWZmzGZm/2aZAGaZM2aZZmaZmWaZzGaZ/2bMAGbMM2bMZmbMmWbMzGbM/2b/AGb/M2b/Zmb/mWb/zGb//5kAAJkAM5kAZpkAmZkAzJkA

/5kzAJkzM5kzZpkzmZkzzJkz/5lmAJlmM5lmZplmmZlmzJlm/5mZAJmZM5mZZpmZmZmZzJmZ/5nMAJnMM5nMZpnMmZnMzJnM/5n/AJn/M5n/Zpn/mZn/zJn//8wAAMwAM8wAZswAmcwAzMwA

/8wzAMwzM8wzZswzmcwzzMwz/8xmAMxmM8xmZsxmmcxmzMxm/8yZAMyZM8yZZsyZmcyZzMyZ/8zMAMzMM8zMZszMmczMzMzM/8z/AMz/M8z/Zsz/mcz/zMz///8AAP8AM/8AZv8Amf8AzP8A

//8zAP8zM/8zZv8zmf8zzP8z//9mAP9mM/9mZv9mmf9mzP9m//+ZAP+ZM/+ZZv+Zmf+ZzP+Z///MAP/MM//MZv/Mmf/MzP/M////AP//M///Zv//mf//zP///yH5BAEAABAALAAAAABQADEAAAj

/AP8JHEiwoMGDCBMqXMiwocOHECNKnEixosWLGDNq3Mixo8ePIEOKHEmypMmTKFOqXJkRBYqBLhfGZPnQ5ct/MxPmpMnQpsCZNm/CfBnTZ86gQ3HeRMoRadGlQpUqJfoUZ9KnVH9GxVhUKtCoVa

WKnZrVK9SmVMPuVHvWrFisPjd+LbuW7tmvb8t6nJuXIFutfbH2lSt07ta/eeOy3clTYuGtjS8yjUy5suXLmDHHdRjWIGPGIjdDBA3YL2SQVY+mvQsVL16yqLOqfuyWtlHZbTv+nY176G67H38

DTs068GrSkoMSN+62+fKQqrW2Xe6aem7CSaf6fq7ceevTmcOLEh9Pvrz58+jTq1/Pvr379+8DAgA7</ThumbNailPhoto>

</orderDetails>

</order>

Hope this article have been informative in getting started. In the next article we will go in details about FOR XML AUTO and how it differs from FOR XML RAW.

 

 

 

 

 

Tags: , ,

SQL Server | XML