Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

My technology blog on SQL Server, TSQL, XML, FILESTREAM and other areas of SQL Server.
Browse by Tags · View All
XML 112
TSQL 69
XQuery 69
XQuery Functions 67
XQuery Training 65
XQuery in TSQL 64
XQuery Tutorial 63
SQL Server XQuery 63
XQuery-Labs 57
BRH 38

Archive · View All
September 2008 32
August 2008 30
July 2008 21
August 2009 19
June 2009 19
May 2010 18
January 2009 15
January 2010 14
October 2008 14
June 2008 13

FOR XML Workshop – Understanding FOR XML RAW

Apr 16 2010 11:38AM by Jacob Sebastian   

In the previous workshop we examined the usage of FOR XML AUTO. Today, we will examine FOR XML RAW, which is a bit more powerful when it comes to customizing the structure of XML output. Let us start with looking into the differences between the two.

To run the examples in this post, you need to create the tables by running the script given in the previous workshop.

Naming the elements

One of the visible differences between the two is that FOR XML AUTO will name the elements after the table name or alias name if it has one. On the contrary, FOR XML RAW allows you to specify the element name.

FOR XML AUTO

SELECT OrderNumber, CustomerID 
FROM OrderHeader
FOR XML AUTO
/*
<OrderHeader OrderNumber="SO101" CustomerID="1" />
<OrderHeader OrderNumber="SO102" CustomerID="1" />
*/

FOR XML RAW

SELECT OrderNumber, CustomerID 
FROM OrderHeader
FOR XML RAW('Order')
/*
<Order OrderNumber="SO101" CustomerID="1" />
<Order OrderNumber="SO102" CustomerID="1" />
*/

Note that in the first example, FOR XML AUTO picked up the table name as the name of the element. In the second example, FOR XML RAW allowed us to specify an element name.

Queries with multiple tables

The diffences will become more visible if there are more than one table involved in the query. FOR XML AUTO will generate an element for each table present in the query and create an element hierarchy depending upon the order in which tables appear in the JOIN. FOR XML RAW will generate only one element per row produced by the query.

FOR XML AUTO

SELECT 
	OrderNumber, CustomerNumber
FROM OrderHeader o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
FOR XML AUTO 
/*
<o OrderNumber="SO101">
  <c CustomerNumber="J001" />
</o>
<o OrderNumber="SO102">
  <c CustomerNumber="J001" />
</o>
*/

FOR XML RAW

SELECT 
	OrderNumber, CustomerNumber
FROM OrderHeader o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
FOR XML RAW('Order')
/*
<Order OrderNumber="SO101" CustomerNumber="J001" />
<Order OrderNumber="SO102" CustomerNumber="J001" />
*/

Generating a ROOT element

Just like FOR XML AUTO, FOR XML RAW also supports the ROOT directive that can be used to generate a root element.

SELECT OrderNumber, CustomerID 
FROM OrderHeader
FOR XML RAW('Order'), ROOT('Orders')
/*
<Orders>
  <Order OrderNumber="SO101" CustomerID="1" />
  <Order OrderNumber="SO102" CustomerID="1" />
</Orders>
*/

Generating Column Values as elements

If you would like to generate column values as elements instead of attributes, you can do so by specifying the ELEMENTS directive.

SELECT OrderNumber, CustomerID 
FROM OrderHeader
FOR XML RAW('Order'), ELEMENTS, ROOT('Orders')
/*
<Orders>
  <Order>
    <OrderNumber>SO101</OrderNumber>
    <CustomerID>1</CustomerID>
  </Order>
  <Order>
    <OrderNumber>SO102</OrderNumber>
    <CustomerID>1</CustomerID>
  </Order>
</Orders>
*/

Just like FOR XML AUTO, FOR XML RAW too is relatively very easy to use. It gives a little bit more control over the structure of the output. Other directives (PATH and EXPLICIT) offers much more control over the output and allows you to better customize the shape of the XML output. We will see more examples of PATH and EXPLICIT in the future posts.

Tags: XML, FOR_XML, BRH, for xml workshop, for xml raw,


Jacob Sebastian
1 · 100% · 32220
0
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]