SQL 301-FOR XML RAW

Posted on by slowder

OK, I need to study my XML queries, so I’m going to write them up as I go.  That way you can learn along with me.  In a normal query we return the results in either datagrid or text view, right.  Well, if we want to transmit the results to another user or a process, more than likely we’re going to want those results in XML.  By adding FOR XML to the end of our queries we can do that.

But don’t get too far ahead.  There are four basic modes to FOR XML.  You have RAW, AUTO, EXPLICIT, or PATH.  We’re going to start with RAW.

I’m going to work out of the adventureworks database on my SQL 2008 R2 instance.  Consider the following query:

SELECT TOP 5
     ContactID, Title, FirstName, MiddleName, LastName, Suffix
FROM Person.Contact

In datagrid mode you’d get the following results.

But if you add FOR XML RAW to the end your query:

SELECT TOP 5
     ContactID, Title, FirstName, MiddleName, LastName, Suffix
FROM Person.Contact
FOR XML RAW

Then you get something different

A link... Click it! It's not spam, I promise!

When you click the link, you should see something like this:

<row ContactID="1" Title="Mr." FirstName="Gustavo" LastName="Achong" />
<row ContactID="2" Title="Ms." FirstName="Catherine" MiddleName="R." LastName="Abel" />
<row ContactID="3" Title="Ms." FirstName="Kim" LastName="Abercrombie" />
<row ContactID="4" Title="Sr." FirstName="Humberto" LastName="Acevedo" />
<row ContactID="5" Title="Sra." FirstName="Pilar" LastName="Ackerman" />

It’s valid XML, but it’s not an XML document, it’s only an XML Fragment.  There is no root node, each element is called “row”, your columnNames become the attribute names, and the values… well they’re still values.  Notice  the first, third, fourth, and last rows.  They have no Middle name values.  That’s because when you’re in RAW mode, if a value is NULL, then that attribute is skipped in the output.

If we wanted to overcome the lack of a root node, we could do that by adding the ROOT(‘name’) argument to our query like this:

SELECT TOP 5
     ContactID, Title, FirstName, MiddleName, LastName, Suffix
FROM Person.Contact
FOR XML RAW, ROOT('Contacts')
--after clicking the link in SSMS you'd get:
<Contacts>
 <row ContactID="1" Title="Mr." FirstName="Gustavo" LastName="Achong" />
 <row ContactID="2" Title="Ms." FirstName="Catherine" MiddleName="R." LastName="Abel" />
 <row ContactID="3" Title="Ms." FirstName="Kim" LastName="Abercrombie" />
 <row ContactID="4" Title="Sr." FirstName="Humberto" LastName="Acevedo" />
 <row ContactID="5" Title="Sra." FirstName="Pilar" LastName="Ackerman" />
</Contacts>

If you want to take this a step further, and change the name of the elements, the RAW keyword can accept an argument that will change the name of the rows.  Let’s change it to Contact, since each row represents one contact.

SELECT TOP 5
     ContactID, Title, FirstName, MiddleName, LastName, Suffix
FROM Person.Contact
FOR XML RAW('Contact'), ROOT('Contacts')
--again, after clicking the link in SSMS, you see:
<Contacts>
 <Contact ContactID="1" Title="Mr." FirstName="Gustavo" LastName="Achong" />
 <Contact ContactID="2" Title="Ms." FirstName="Catherine" MiddleName="R." LastName="Abel" />
 <Contact ContactID="3" Title="Ms." FirstName="Kim" LastName="Abercrombie" />
 <Contact ContactID="4" Title="Sr." FirstName="Humberto" LastName="Acevedo" />
 <Contact ContactID="5" Title="Sra." FirstName="Pilar" LastName="Ackerman" />
</Contacts>

Now the last step of customization I can find for XML RAW is to change the attributes into elements.  All you have to do is add one more keyword to the query: ELEMENTS.

SELECT TOP 5
     ContactID, Title, FirstName, MiddleName, LastName, Suffix
FROM Person.Contact
FOR XML RAW('Contact'), ROOT('Contacts'), ELEMENTS
--And after clicking the link in SSMS, you see:
<Contacts>
 <Contact>
  <ContactID>1</ContactID>
  <Title>Mr.</Title>
  <FirstName>Gustavo</FirstName>
  <LastName>Achong</LastName>
 </Contact>
 <Contact>
  <ContactID>2</ContactID>
  <Title>Ms.</Title>
  <FirstName>Catherine</FirstName>
  <MiddleName>R.</MiddleName>
  <LastName>Abel</LastName>
 </Contact>
 <Contact>
  <ContactID>3</ContactID>
  <Title>Ms.</Title>
  <FirstName>Kim</FirstName>
  <LastName>Abercrombie</LastName>
 </Contact>
 <Contact>
 <ContactID>4</ContactID>
  <Title>Sr.</Title>
  <FirstName>Humberto</FirstName>
  <LastName>Acevedo</LastName>
 </Contact>
 <Contact>
 <ContactID>5</ContactID>
  <Title>Sra.</Title>
  <FirstName>Pilar</FirstName>
  <LastName>Ackerman</LastName>
 </Contact>
</Contacts>

Elements can also take an option, XSINIL, if you want to see all the elements, even those that have NULL values, then you want to add XSINIL, and you’ll get the elements, along with a XML tag defining the tag as null.  I’m not sure why you’d want this, since not there and NULL, are pretty much the same, but I’m sure someone has a parser that will blow up if you don’t include every element.

So Let’s see the query:

SELECT TOP 5
     ContactID, Title, FirstName, MiddleName, LastName, Suffix
FROM Person.Contact
FOR XML RAW('Contact'), ROOT('Contacts'), ELEMENTS XSINIL
--one last time clicking the link
<Contacts xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <Contact>
  <ContactID>1</ContactID>
  <Title>Mr.</Title>
  <FirstName>Gustavo</FirstName>
  <MiddleName xsi:nil="true" />
  <LastName>Achong</LastName>
  <Suffix xsi:nil="true" />
 </Contact>
 <Contact>
 <ContactID>2</ContactID>
  <Title>Ms.</Title>
  <FirstName>Catherine</FirstName>
  <MiddleName>R.</MiddleName>
  <LastName>Abel</LastName>
  <Suffix xsi:nil="true" />
 </Contact>
 <Contact>
  <ContactID>3</ContactID>
  <Title>Ms.</Title>
  <FirstName>Kim</FirstName>
  <MiddleName xsi:nil="true" />
  <LastName>Abercrombie</LastName>
  <Suffix xsi:nil="true" />
 </Contact>
 <Contact>
  <ContactID>4</ContactID>
  <Title>Sr.</Title>
  <FirstName>Humberto</FirstName>
  <MiddleName xsi:nil="true" />
  <LastName>Acevedo</LastName>
  <Suffix xsi:nil="true" />
 </Contact>
 <Contact>
  <ContactID>5</ContactID>
  <Title>Sra.</Title>
  <FirstName>Pilar</FirstName>
  <MiddleName xsi:nil="true" />
  <LastName>Ackerman</LastName>
  <Suffix xsi:nil="true" />
 </Contact>
</Contacts>

If you hit up the books online, You’ll find three more options.  I didn’t see anything about this in the Self-Paced book.  I don’t recall seeing any questions on it either but, for completeness:

If you wanted to include a schema in your results you have two options, XMLDATA or XMLSCHEMA.  If you want to use these, you cannot name your row elements (don’t pass a name to the RAW(”) option, and you cannot wrap your results in a ROW(‘name’).

If you’re going to return binary data (images anyone?), then you’ll have to use the BINARY BASE64 option.  That way it’ll encode the binary data so it can be included in your XML.

Now, that’s all the options I can find for the FOR XML RAW statement.

  • RAW accepts a name parameter, RAW(‘name’), that name will be the element name for each row.
  • You can add ROOT(‘name’), this will wrap the entire results set in a root named whatever you pass.
  • If you want to change the columns from attributes of each row element, to children elements, add the ELEMENTS keyword.
  • If you want to show every element, even those storing NULL values, add the XSINIL keyword.
  • You have two options for including schema data, XMLDATA and XMLSCHEMA, but be warned you can’t use a root node, or name your row elements.
  • If you’re going to include binary data in your results, be sure to add the BINARY BASE64 option.

That’s it!  Next time, we’re going to cover FOR XML AUTO.  Fewer options, but it handles hierarchical data (at least simple hierarchical data) right out of the box.

Play around with these options.  Learn when you want to include each of the options, and you’ll be better prepared for the 70-433!

Do you have any questions?  If so, send them in.  I’m here to help in any way that I can!

Leave a Reply

Your email address will not be published. Required fields are marked *