Prev: String Manipulation Func
Next: how to delete/archive huge data from a table without much issu
From: Roy Goldhammer on 20 Jun 2010 11:07
Hello there

I have an sql process which getting XML from one source or more.

IT should get the XML and its attributes, nodes, ect and alter it to a
diffrerent xml

for example

declare @XM xml

set @xml = (select Product_id, product_name
from products
for xml auto)

the result shoud be:
<Products total="30">
<Product Product_ID="1" ... />
<Product Product_ID="2" ... />
</Products>

in essuming i don't know the structure first how can i know what is the name
of the node, its attributes?


From: John Bell on 20 Jun 2010 12:15
On Sun, 20 Jun 2010 18:07:24 +0300, "Roy Goldhammer" <royg(a)yahoo.com>
wrote:

>Hello there
>
>I have an sql process which getting XML from one source or more.
>
>IT should get the XML and its attributes, nodes, ect and alter it to a
>diffrerent xml
>
>for example
>
>declare @XM xml
>
>set @xml = (select Product_id, product_name
>from products
>for xml auto)
>
>the result shoud be:
><Products total="30">
> <Product Product_ID="1" ... />
> <Product Product_ID="2" ... />
></Products>
>
>in essuming i don't know the structure first how can i know what is the name
>of the node, its attributes?
>
Ideally your data will conform to a schema even if it is not typed, if
it doesn't then you are really asking for problems.

You can get the edge table which will give you structure information,
but that is not going to tell you how to map it onto the database
schema. See example F in
http://msdn.microsoft.com/en-us/library/ms187897.aspx

John
From: Erland Sommarskog on 20 Jun 2010 16:53
Roy Goldhammer (royg(a)yahoo.com) writes:
> I have an sql process which getting XML from one source or more.
>
> IT should get the XML and its attributes, nodes, ect and alter it to a
> diffrerent xml
>
> for example
>
> declare @XM xml
>
> set @xml = (select Product_id, product_name
> from products
> for xml auto)
>
> the result shoud be:
><Products total="30">
> <Product Product_ID="1" ... />
> <Product Product_ID="2" ... />
></Products>
>
> in essuming i don't know the structure first how can i know what is the
> name of the node, its attributes?

Here is a query that gives you the result you ask for:

SELECT COUNT(*) AS [@total],
(SELECT ProductID AS [@ProductID],
ProductName AS [@ProductName]
FROM Products
FOR XML PATH('Product'), TYPE) AS [node()]
FROM Products
FOR XML PATH('Products')

It took me some trial and error to achieve this result. Rather to explain
it I will refer you to the topic "Using PATH Mode" in Books Online. And
don't be worried, if you don't grasp all on first reading - I have still
things to learn myself. (I think this was the first time I ever used
node().)



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: String Manipulation Func
Next: how to delete/archive huge data from a table without much issu