Question on FOR XML issue

[intrepid_dw]

Hi, all.

I'm trying to become smarter about the use of the FOR XML clause in SQL
Server. There's one question I'd like to ask that I've not found
directly answered elsewhere, probably due to my own inaccurate or
feeble (or both) searching, so I thought I'd ask the good folks here
for a little information if I may.

I've played with the FOR XML AUTO, ELEMENTS variation of the XML
capability, and it is marvelous for what I would call "cleanly"
hierarchical data, and by that I naively mean always one-to-many
relationships.

The data I'm trying to model has, at a basic level, several one-to-many
relationships, eg Parent has many children of type X, and many children
of type Y. X and Y, themselves, are unrelated. When I write the query
to dump this data, however, SQL Server tries to wrap Y records as
children of X records, which is not correct. Another way of thinking
about it might be a list of PERSONS, and there is a BOOKS table of
favorite books for each person, and a table of favorite TV shows for
each person. BOOKS and TV are unrelated.

EG

Select Person.Lastname,Parent.Firstname,Books.Title,TV.Sh owTitle
from Person
join BOOKS
on Person.PersonID=Books.PersonID
join TV
on Person.PersonID=TV.PersonID
for XML AUTO, ELEMENTS

And I'd like to see:

<PERSON>
<Lastname>somevalue</Lastname>
<Firstanme>somevalue</Lastname>
<BOOKS>
<TITLE>Moby Dick</TITLE>
</BOOKS>
<BOOKS>
<TITLE>Hunt for Red October</TITLE>
</BOOKS>
<TV>
<ShowTItle>Laverne and Shirley</ShowTitle>
</TV>
</PERSON>

But I get something more on the order of this:

<PERSON>
<Lastname>somevalue</Lastname>
<Firstname>somevalue</Firstname>
<BOOKS>
<TITLE>MOBY DICK</TITLE>
<TV>
<SHOWTITLE>Laverne and Shirley</ShowTitle>
</TV>
</BOOKS>
</PERSON>
At a first glance, it doesn't seem to me there's a way to solve
this, because the joins inevitably create the appearance of a
relationship between the two "inner" pieces of data even though it
doesn't really exist. Perhaps I'm merely writing the query incorrectly,
but as I try to think of ways to rearrange it, it seems I'm just moving
the same problem around. Is there a way to solve it? Would it be
smarter just to break the queries up into separate XML exports, eg a
BOOKS.XML and a TV.XML, then combine them?

If I'm being unyieldingly stupid in not seeing the answer to this,
please accept my humble apologies in advance.

Your help is greatly appreciated. I would ask that replies be posted to
the newsgroup; the email referenced in this message is dead.

Thanks again,
David

[Simon Hayes]

<in*********@hotmail.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...

Hi, all.

I'm trying to become smarter about the use of the FOR XML clause in SQL
Server. There's one question I'd like to ask that I've not found
directly answered elsewhere, probably due to my own inaccurate or
feeble (or both) searching, so I thought I'd ask the good folks here
for a little information if I may.

I've played with the FOR XML AUTO, ELEMENTS variation of the XML
capability, and it is marvelous for what I would call "cleanly"
hierarchical data, and by that I naively mean always one-to-many
relationships.

<snip>

I don't have an answer myself, but you might get a better response in
microsoft.public.sqlserver.xml

Simon

[Erland Sommarskog]

(in*********@hotmail.com) writes:

The data I'm trying to model has, at a basic level, several one-to-many
relationships, eg Parent has many children of type X, and many children
of type Y. X and Y, themselves, are unrelated. When I write the query
to dump this data, however, SQL Server tries to wrap Y records as
children of X records, which is not correct. Another way of thinking
about it might be a list of PERSONS, and there is a BOOKS table of
favorite books for each person, and a table of favorite TV shows for
each person. BOOKS and TV are unrelated.

I'm by no means an expert in XML, but I would guess that you need to
use FOR XML EXPLICIT in this case. Below is an example for your situation.
I should add that I mainly made this example for my own exercise, so
this may not be the "proper" way to write it.

SELECT 1 as Tag,
NULL as Parent,
OrderID as [Order!1!OrderID],
NULL as [Customer!2!CustomerID!element],
NULL as [Customer!2!CompanyName!element],
NULL as [Customer!2!City!element],
NULL as [Employee!3!FirstName!element],
NULL as [Employee!3!LastName!element]
FROM Orders
WHERE OrderID BETWEEN 11000 AND 11020
UNION ALL
select 2, 1,
Orders.OrderID, c.CustomerID, c.CompanyName, c.City, NULL, NULL
from Orders
join Customers c ON Orders.CustomerID = c.CustomerID
WHERE Orders.OrderID BETWEEN 11000 AND 11020
UNION ALL
select 3, 1, o.OrderID, NULL, NULL, NULL, e.FirstName, e.LastName
from Orders o
join Employees e ON o.EmployeeID = e.EmployeeID
WHERE o.OrderID BETWEEN 11000 AND 11020
ORDER BY [Order!1!OrderID], Tag
FOR XML EXPLICIT
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[intrepid_dw]

Erland:

The more I study this, the more I think you're right on the money. I
suspected that the problem was at either end of the spectrum; either I
wrote the query horribly wrong to begin with, and was missing something
obvious, or I needed to use the EXPLICIT alternative and the universal
table. Unfortunately, the universal table and query that would be
necessary for the data I'm actually modeling would be HUGE and just
about incomprehensible.

I think what I'm going to do is to design queries to return the "pure"
one-to-many relationships, and then merge the resultant XML into a
larger file. That's not at all complicated and strikes me as a
substantially smarter alternative than trying to write the ELEMENTS
version of this query.

As an irrelevant aside, its interesting to design an XML layout for
existing data because it out almost reminds me of designing a grammar,
eg decompositions, generations of repetitive sections, etc.

Thanks for your assistance, Erland!

-David

Erland Sommarskog wrote:

(in*********@hotmail.com) writes:

The data I'm trying to model has, at a basic level, several one-to-many relationships, eg Parent has many children of type X, and many children of type Y. X and Y, themselves, are unrelated. When I write the query to dump this data, however, SQL Server tries to wrap Y records as
children of X records, which is not correct. Another way of thinking about it might be a list of PERSONS, and there is a BOOKS table of
favorite books for each person, and a table of favorite TV shows for each person. BOOKS and TV are unrelated.
I'm by no means an expert in XML, but I would guess that you need to
use FOR XML EXPLICIT in this case. Below is an example for your

situation. I should add that I mainly made this example for my own exercise, so
this may not be the "proper" way to write it.

SELECT 1 as Tag,
NULL as Parent,
OrderID as [Order!1!OrderID],
NULL as [Customer!2!CustomerID!element],
NULL as [Customer!2!CompanyName!element],
NULL as [Customer!2!City!element],
NULL as [Employee!3!FirstName!element],
NULL as [Employee!3!LastName!element]
FROM Orders
WHERE OrderID BETWEEN 11000 AND 11020
UNION ALL
select 2, 1,
Orders.OrderID, c.CustomerID, c.CompanyName, c.City, NULL, NULL from Orders
join Customers c ON Orders.CustomerID = c.CustomerID
WHERE Orders.OrderID BETWEEN 11000 AND 11020
UNION ALL
select 3, 1, o.OrderID, NULL, NULL, NULL, e.FirstName, e.LastName
from Orders o
join Employees e ON o.EmployeeID = e.EmployeeID
WHERE o.OrderID BETWEEN 11000 AND 11020
ORDER BY [Order!1!OrderID], Tag
FOR XML EXPLICIT
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp