How can I convert xml into table using SQL Server 2005?

How can I convert the xml as:
<row>
<a>1</a>
<b>2</b>
<c>3</c>
<d>4</d>
...
...
...
</row>
into table
a b c d ... ... ...
1 2 3 4 ... ... ...
ABC wrote:
> How can I convert the xml as:
> <row>
> <a>1</a>
> <b>2</b>
> <c>3</c>
> <d>4</d>
> ...
> ...
> ...
> </row>
> into table
> a b c d ... ... ...
> --
> 1 2 3 4 ... ... ...
This is an example using the stored procedure sp_xml_preparedocument and
the rowset provider OPENXML:
DECLARE @.x xml;
SET @.x = '<row>
<a>1</a>
<b>2</b>
<c>3</c>
<d>4</d>
</row>';
DECLARE @.iDoc int;
EXEC sp_xml_preparedocument @.iDoc OUTPUT, @.x;
SELECT *
FROM OPENXML(@.iDoc, '/row', 2)
WITH (a int, b int, c int, d int);
EXEC sp_xml_removedocument @.iDoc;
Another approach is to use the XQuery nodes function as follows:
DECLARE @.x xml;
SET @.x = '<row>
<a>1</a>
<b>2</b>
<c>3</c>
<d>4</d>
</row>';
SELECT T.col.value('a[1]', 'int') AS a,
T.col.value('b[1]', 'int') AS b,
T.col.value('c[1]', 'int') AS c,
T.col.value('d[1]', 'int') AS d
FROM @.x.nodes('/row') AS T(col);
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
|||Thanks, but I have problem if the number of tag under the row node is
dynamic, it is hard apply this method.
"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:u8HuKRkvHHA.356@.TK2MSFTNGP02.phx.gbl...
> ABC wrote:
> This is an example using the stored procedure sp_xml_preparedocument and
> the rowset provider OPENXML:
> DECLARE @.x xml;
> SET @.x = '<row>
> <a>1</a>
> <b>2</b>
> <c>3</c>
> <d>4</d>
> </row>';
> DECLARE @.iDoc int;
> EXEC sp_xml_preparedocument @.iDoc OUTPUT, @.x;
> SELECT *
> FROM OPENXML(@.iDoc, '/row', 2)
> WITH (a int, b int, c int, d int);
> EXEC sp_xml_removedocument @.iDoc;
>
> Another approach is to use the XQuery nodes function as follows:
> DECLARE @.x xml;
> SET @.x = '<row>
> <a>1</a>
> <b>2</b>
> <c>3</c>
> <d>4</d>
> </row>';
> SELECT T.col.value('a[1]', 'int') AS a,
> T.col.value('b[1]', 'int') AS b,
> T.col.value('c[1]', 'int') AS c,
> T.col.value('d[1]', 'int') AS d
> FROM @.x.nodes('/row') AS T(col);
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/
|||ABC wrote:
> but I have problem if the number of tag under the row node is
> dynamic, it is hard apply this method.
That is true, I am not sure how to solve that case.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
|||It can't be completely dymanic for two reasons...
The first is XML should conform to a fixed schema, and secondly,
you're trying to push data into a fixed table.
On Thu, 5 Jul 2007 09:43:12 +0800,
"ABC" <abc@.abc.com> wrote in message
news:OXjNdXqvHHA.4516@.TK2MSFTNGP06.phx.gbl

> Thanks, but I have problem if the number of tag under the row node is
> dynamic, it is hard apply this method.
>
>
> "Martin Honnen" <mahotrash@.yahoo.de> wrote in message
> news:u8HuKRkvHHA.356@.TK2MSFTNGP02.phx.gbl...
>
sql