Transforms Rows Into XML Elements: FOR XML RAW

In this post we'll learn how to transforms rows from a table or queries results to XML using the FOR XML RAW.

FOR XML RAW

By default:

No root element

Attribute-centric presentation (all data is display as attributes)

All row tag name = row

Exclude NULL mark

      Use  ELEMENTS XSINIL directive to include NULL marks.

Doesn't include nesting (XML document/fragment is format flat).

We'll go through a series of examples and explore the FOR XML RAW.

--
-- When no directive is specified after the RAW keyword, this query uses 
--   default setting above for the result: 
--     * use attribute-centric presentation(row data is stored inside row tag)
--     * no nesting
--     * no root node
--     * all XML elements' name = row
--     * NULL marks are excluded. 
USE TSQL2012;
GO

SELECT  c.custid
       ,c.contactname
FROM    Sales.Customers AS c
WHERE   c.custid <= 2
FOR XML RAW;

Result: Notice that the result is flat - no nesting. 
<row custid="1" contactname="Allen, Michael" />
<row custid="2" contactname="Hassall, Mark" />

--
-- This query add nesting to the result by adding the ELEMENTS directive, root node. 
-- Notice we change row tag name from row to Customer. 
SELECT  c.custid
       ,c.contactname
FROM    Sales.Customers AS c
WHERE   c.custid <= 2
FOR XML RAW('Customer')    -- Change the row tag name from row to Customer 
       ,ELEMENTS           -- Use element-centric. 
       ,ROOT('Customers'); -- Root node with name = Customers. 
                           -- Note Root node's name cannot be an empty string. 
                           -- If ELEMENT directive is used but exclude the ROOT directive,
                           --   the result is a XML fragment (has no root node). 
Result
<Customers>
  <Customer>
    <custid>1</custid>
    <contactname>Allen, Michael</contactname>
  </Customer>
  <Customer>
    <custid>2</custid>
    <contactname>Hassall, Mark</contactname>
  </Customer>
</Customers>

-- If we specify an empty string for the RAW mode argument, 
-- all rows tag name will be removed. 
-- Note: In this case, we also need to include ELEMENT directive, 
-- else error will occurs. 
SELECT  c.custid
       ,c.contactname
FROM    Sales.Customers AS c
WHERE   c.custid <= 2
FOR XML RAW('') -- Remove all rows tag name 
       ,ELEMENTS 
       ,ROOT('Customers');

Result:
<Customers>
  <custid>1</custid>
  <contactname>Allen, Michael</contactname>
  <custid>2</custid>
  <contactname>Hassall, Mark</contactname>
</Customers>

-- This query will yield an error because by default XML RAW mode 
-- use attribute-centric presentation, and attributes are enclosed 
-- in the row tags. 
-- Therefore if the row tag are removed(by specified empty string for the RAW's argument)
--  and ELEMENT directive is not specified,
-- so there's no place to store the attributes.
SELECT  c.custid
       ,c.contactname
FROM    Sales.Customers AS c
WHERE   c.custid <= 2
FOR XML RAW('') -- Remove all rows tag name 
       ,ROOT('Customers');

Result: 
Msg 6864, Level 16, State 1, Line 1 
Row tag omission (empty row tag name) cannot be used 
    with attribute-centric FOR XML serialization.

--This query will yield an error because root tag name 
--   can't be an empty string.
SELECT  c.custid
       ,c.contactname
FROM    Sales.Customers AS c
WHERE   c.custid <= 2
FOR XML RAW
       ,ELEMENTS 
       ,ROOT('');

Result:
Msg 6861, Level 16, State 1, Line 1
Empty root tag name cannot be specified with FOR XML.

-- This query include NULL marks and convert result to XML type.
SELECT  c.custid
       ,c.contactname
       ,c.fax
FROM    Sales.Customers AS c
WHERE   c.custid = 3
FOR XML RAW('Customer') -- Change the row tag name from row to Customer 
       ,ELEMENTS XSINIL -- Indent the XML elements, use element-centric, and include NULL mark 
       ,TYPE            -- Convert to result to XML type
       ,ROOT('Customers');

Result: Notice customer with custid equals 3, has missing fax number. 
 The result of missing fax is display as: <fax xsi:nil="true" />
<Customers xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Customer>
    <custid>3</custid>
    <contactname>Peoples, John</contactname>
    <fax xsi:nil="true" />
  </Customer>
</Customers>

--
-- This query uses XMLDATA directive to return the XML-Data schema that
--   describe the XML document structure.
SELECT  c.custid
       ,c.contactname
FROM    Sales.Customers AS c
WHERE   c.custid <= 2
FOR XML RAW
       ,ELEMENTS 
       ,XMLDATA; -- return the XML-Data schema

Result:
<Schema name="Schema2" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
  <ElementType name="row" content="eltOnly" model="closed" order="many">
    <element type="custid" />
    <element type="contactname" />
  </ElementType>
  <ElementType name="custid" content="textOnly" model="closed" dt:type="i4" />
  <ElementType name="contactname" content="textOnly" model="closed" dt:type="string" />
</Schema>
<row xmlns="x-schema:#Schema2">
  <custid>1</custid>
  <contactname>Allen, Michael</contactname>
</row>
<row xmlns="x-schema:#Schema2">
  <custid>2</custid>
  <contactname>Hassall, Mark</contactname>
</row>

 
Reference:
Use RAW Mode with FOR XML: http://technet.microsoft.com/en-us/library/ms175140.aspx
70-461 Training Kit