XML and SqlServer

  • Thread starter Thread starter Jim Heavey
  • Start date Start date
J

Jim Heavey

Hello, I trying to use the For XML for the first time and am trying to
figure out the "Explicit" option. Going over the example in the
documentation, they show a sample which looks like the following:

SELECT 1 as Tag,
NULL as Parent,
Customers.CustomerID as [Customer!1!CustomerID],
NULL as [Order!2!OrderID]
FROM Customers

I am hoping someone can confirm my interpertation here. The first line,
the "1" is used merely for sorting purposes to sort the "structure" in
front of the data. The second line is specifying that for the first
field retrieved, it has no parent. The 3rd line is telling that it
should map the "CustomerID" field as an attribute of the Customer
Element. The forth line is saying to map an attribute of OrderID to a
Element of Order.

Is that about right?

Questions..
Why does the 4th line specify "Null" while the 3rd specifies
"Customers.CustomerID". I guess I really do not understand how to
properly use this column.

In the output, below - where did "cid=C1" come from? I am guessing this
is CustomerID field? How did it get associated with "cid". Does the
"pid='P1'" is this related to the Tag Number? Why is it needed?

This is the output they are creating in this example...

<Customer cid="C1" name="Janine">
<Order id="O1" date="1/20/1996">
<OrderDetail id="OD1" pid="P1"/>
<OrderDetail id="OD2" pid="P2"/>
</Order>
<Order id="O2" date="3/29/1997">
...
</Customer>

Thanks in advance for your assistance!!!!!!!
 
If you simply want XML output, you can do like so:

SELECT column1, column2
FROM Table
FOR XML RAW|AUTO

To nest from multiple tables and control the output you end up having to do
the strange syntax. Basically, you are building a record that nests and you
have to put placeholders in the child queries (the 1 you are seeing, for
example). The NULLs are for record elements in one query that do not exist
in the other. It gets rather complex and you will screw the first few
queries up royally (unless you are unlike anyone I have met).

To demistify the FOR XML, I recommend the following (for starters):
http://www.eggheadcafe.com/articles/20030804.asp
http://www.microsoft.com/mspress/books/sampchap/5178a.asp

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************
 
Back
Top