Tuesday, July 17, 2012

Getting XML output from SQL Query - Part 1

With SQL Server 2005, SQL has added an interesting and useful element "FOR XML". FOR XML returns the output of a sql query in the XML format. In the following 2 part series, I will try to explain the basics of using the FOR XML and some nuances attached with it.

The syntax for FOR XML as per the msdn site is as below,
[ FOR { BROWSE |  } ]
 ::=
XML 
    { 
      { RAW [ ('ElementName') ] | AUTO } 
        [ 
            
           [ , { XMLDATA | XMLSCHEMA [ ('TargetNameSpaceURI') ]} ] 
           [ , ELEMENTS [ XSINIL | ABSENT ] 
        ]
      | EXPLICIT 
        [ 
            
           [ , XMLDATA ] 
        ]
      | PATH [ ('ElementName') ] 
        [ 
            
           [ , ELEMENTS [ XSINIL | ABSENT ] ]
        ]
     } 
 
  ::= 
   [ , BINARY BASE64 ]
   [ , TYPE ]
   [ , ROOT [ ('RootName') ] ]
While going through each element is beyond the scope of this post, very quickly I will explain the elements. Note that in the above syntax, the elements separated by "|" follow an OR relationship and cannot be used   together.
RAW: If the "ElementName" is unspecified, then the result set has a generic identifier "row"
AUTO: In this mode, the data is formatted based on the table and the columns selected in the SELECT query.
EXPLICIT: With this directive, one can shape the XML to their requirements, but great care is needed and, it is not as simple as it looks. You can also use CDATA with the EXPLICIT directive.
PATH: A simpler mode of creating additional nesting for representing complex properties. By default the    "row" element is used as the parent identifier but this can be changed as required
XMLDATA: With this the schema/document structure can be added to the resulting XML
XMLSCHEMA: With this the XSD schema is added to the resulting XML
ELEMENTS: When specified the columns are returned as subelements. In absence of this, the columns areappended to the parent as attributes
XSINIL:Specifying this allows the data to be returned as xsi:nil="true" in case the column value is null 
ABSENT: This indicates that the columns with null values will not be added to the resulting XML
BINARY BASE64: Returns the specified data in a binary base64 encoded format
TYPE: Indicates that the query should return data as an xml type
ROOT: Specifies the top level element for the resulting XML. By default it is "root", but one can specify thename for the root node.


No comments :