Integration with the XML Data Type

With the introduction of the XML data type, we wanted to also give FOR XML the ability to generate an instance of XML directly (more precisely, it generates a single row, single column rowset where the cell contains the XML data type instance).

Because of the backwards-compatibility considerations outlined above, we added a new TYPE directive to generate the result as XML. For example,

DECLARE @XMLVAR XML

SET @XMLVAR = (SELECT SALENO,SALEDT FROM TFA_SALEHDR X

                      FOR XML AUTO)

This result is guaranteed to conform to the well-formedness constraints provided by the XML data type. Since the result is an XML data type instance, you can also use XQuery expressions to query and reshape the result. For example, the following expression retrieves the TFA_SALEHDR contact name into a new x element.

SELECT (SELECT SALENO,SALEDT FROM TFA_SALEHDR x 

FOR XML AUTO, TYPE).query(

'<doc>{

   for $c in /x

   return

     <x SALENO="{data($c/@SALENO)}" SALEDT="{data($c/@SALEDT)}"/>

 }</doc>')

returns (first elements 2^nd elements shown),

<doc>

  <x SALENO="VSPD/00001/11-12" SALEDT="2011-06-20T00:00:00" />

  <x SALENO="VSPD/00002/11-12" SALEDT="2011-06-20T00:00:00" />

  <x SALENO="VSPD/00003/11-12" SALEDT="2011-06-20T00:00:00" />

  <x SALENO="VSPD/00004/11-12" SALEDT="2011-06-23T00:00:00" />

</doc>

returns the TFA_SALEHDR elements as an XML data type instance, instead of the nvarchar (max) instance that would have been the case without the TYPE directive.

We can read the XML file using the SELECT statement. Following is the XML which we will read using T-SQL:

<doc>

  <x SALENO="VSPD/00001/11-12" SALEDT="2011-06-20T00:00:00" />

  <x SALENO="VSPD/00002/11-12" SALEDT="2011-06-20T00:00:00" />

  <x SALENO="VSPD/00003/11-12" SALEDT="2011-06-20T00:00:00" />

  <x SALENO="VSPD/00004/11-12" SALEDT="2011-06-23T00:00:00" />

</doc>

Following is the T-SQL script which we will be used to read the XML:

SELECT FROM_XML.ID.value('@SALENO','VARCHAR(30)') AS SALENO,

       FROM_XML.ID.value('@SALEDT','DATETIME') AS SALEDT

FROM @XMLVAR.nodes('./X') as FROM_XML(ID)

Posted by: MR. JOYDEEP DAS