|
Home > Archive > MS SQL XML > November 2005 > undesired element nesting
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
undesired element nesting
|
|
|
| If I run the following SQL against Northwind:
SELECT * FROM Products INNER JOIN
Suppliers ON Products.SupplierID =
Suppliers.SupplierID INNER JOIN
Categories ON Products.CategoryID =
Categories.CategoryID
where ProductID = 1
for xml auto, elements
the Categories element is nested within the Suppliers element, like this:
<root>
<Products>
<Suppliers>
<Categories>
</Categories>
</Suppliers>
</Products>
</root>
But it should be like this:
<root>
<Products>
<Suppliers>
</Suppliers>
<Categories>
</Categories>
</Products>
</root>
because Categories is not related to Suppliers.
How can I cause the XML output to be nested correctly?
Thanks
Bill
| |
|
| I'm using SQL Server 2000
"bill" <belgie@datamti.com> wrote in message
news:OuSa8DK4FHA.4076@TK2MSFTNGP15.phx.gbl...
> If I run the following SQL against Northwind:
>
> SELECT * FROM Products INNER JOIN
> Suppliers ON Products.SupplierID =
> Suppliers.SupplierID INNER JOIN
> Categories ON Products.CategoryID =
> Categories.CategoryID
> where ProductID = 1
> for xml auto, elements
>
> the Categories element is nested within the Suppliers element, like this:
>
> <root>
> <Products>
> <Suppliers>
> <Categories>
> </Categories>
> </Suppliers>
> </Products>
> </root>
>
> But it should be like this:
>
> <root>
> <Products>
> <Suppliers>
> </Suppliers>
> <Categories>
> </Categories>
> </Products>
> </root>
>
> because Categories is not related to Suppliers.
>
> How can I cause the XML output to be nested correctly?
>
> Thanks
> Bill
>
>
| |
| Michael Rys [MSFT] 2005-11-04, 3:23 am |
| The heuristic AUTO mode cannot be used to generate these sibling
relationships in SQL Server 2000. You will have to use the EXPLICIT mode in
SQL Server 2000 (see my weblog at http://sqljunkies.com/weblog/mrys for some
examples (search for code samples) or for the link to an MSDN whitepaper on
FOR XML in SQL Server 2005 that also shows you EXPLICIT mode examples).
Another alternative is to upgrade to SQL Server 2005 where you can use
nested AUTO or PATH mode queries to address this issue in an easier way :-).
Best regards
Michael
"bill" <belgie@datamti.com> wrote in message
news:OuSa8DK4FHA.4076@TK2MSFTNGP15.phx.gbl...
> If I run the following SQL against Northwind:
>
> SELECT * FROM Products INNER JOIN
> Suppliers ON Products.SupplierID =
> Suppliers.SupplierID INNER JOIN
> Categories ON Products.CategoryID =
> Categories.CategoryID
> where ProductID = 1
> for xml auto, elements
>
> the Categories element is nested within the Suppliers element, like this:
>
> <root>
> <Products>
> <Suppliers>
> <Categories>
> </Categories>
> </Suppliers>
> </Products>
> </root>
>
> But it should be like this:
>
> <root>
> <Products>
> <Suppliers>
> </Suppliers>
> <Categories>
> </Categories>
> </Products>
> </root>
>
> because Categories is not related to Suppliers.
>
> How can I cause the XML output to be nested correctly?
>
> Thanks
> Bill
>
>
|
|
|
|
|