Using FOR XML RAW

Using FOR XML RAW

The simplest XML mode to use is RAW . In RAW mode, each row in the resultset corresponds to an element named "row" in the resulting XML document fragment. Each column in the select list that returns a non-null value generates an attribute of "row" named after the column (or column alias) containing its value. This is known as attribute-centric rowset mapping.

Listing 41.1 illustrates the mapping of rows and columns into XML using RAW mode. You can try this from your IE browser or from Query Analyzer.

Listing 41.1 Using FOR XML RAW to Retrieve Customer Data from Northwind

 SELECT CustomerID CID, CompanyName, ContactName FROM Customers WHERE CustomerID = 'ANTON' FOR XML RAW go XML_F52E2B61-18A1-11d1-B105-00805F49916B     --------------------------------------------------------- <row CID="ANTON" CompanyName="Antonio Moreno Taquera" ContactName="Antonio Moreno"/> 

As you can see, RAW mode is designed to generate flat (non-hierarchical) XML that structurally mirrors traditional resultsets. Even though you know, for example, that every CustomerID in Customers is tied to zero or more Orders, a row selected from Customers will never contain a row selected from Orders. Listing 41.2 illustrates how adding a join between Orders and Customers on CustomerID to the previous listing results in a separate row created for each unique Customer-Order combination, but no nesting of XML elements. This is true no matter how many tables are involved.

Listing 41.2 Joining Tables Produces Flat XML in RAW Mode

 SELECT Cu.CompanyName CO, Cu.ContactName CN, O.OrderID FROM Customers Cu JOIN Orders O on O.CustomerID = Cu.CustomerID WHERE Cu.CustomerID = 'ANTON' FOR XML RAW go XML_F52E2B61-18A1-11d1-B105-00805F49916B    ----------------------------------------------------------------------------- <row CO="Antonio Moreno Taquera" CN="Antonio Moreno" OrderID="10365"/> <row CO="Antonio Moreno Taquera" CN="Antonio Moreno" OrderID="10507"/> <row CO="Antonio Moreno Taquera" CN="Antonio Moreno" OrderID="10535"/> <row CO="Antonio Moreno Taquera" CN="Antonio Moreno" OrderID="10573"/> <row CO="Antonio Moreno Taquera" CN="Antonio Moreno" OrderID="10677"/> <row CO="Antonio Moreno Taquera" CN="Antonio Moreno" OrderID="10682"/> <row CO="Antonio Moreno Taquera" CN="Antonio Moreno" OrderID="10856"/> 

RAW mode will not produce the highly structured view of data that is one of XML's biggest gains over unstructured data. But don't be fooled by its simplicity. It is useful simply because it is generic ”you know what to expect. When processing RAW XML using a stylesheet, you can specify rules for transforming row elements produced by a vast number of queries.