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...
>

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

How can I connect to SQL Server 2005 analysis services server programmatically?

Hi, all here,

Would please anyone here give me any idea about how can I connect to SQL Server 2005 analysis services server and send XML request to it programmatically (with Business intelligence development studio in SQL Server 2005)? Thanks a lot.

With best regards,

Yours sincerely,

That's a big topic - you can start here

http://msdn2.microsoft.com/en-us/library/ms186654.aspx

For the XMLA ThinMiner sample look here

http://www.sqlserverdatamining.com/DMCommunity/LiveSamples/124.aspx

Here's a blog entry of someone who's figured it out as well

http://geekswithblogs.net/darrengosbell/archive/2006/05/25/xmlaClient.aspx

|||Hi, Jamie, thanks a lot.

How can I concatenate fields XML in Yukon?

for example:
XML 1: <CAR name="1">
XML 2: <CAR name="2">
XML expected:
<CAR name="1">
<CAR name="2">
Thank.Hello sqlextreme,
declare @.x1 xml,@.x2 xml
set @.x1 = '<CAR name="1"/>'
set @.x2 = '<CAR name="2"/>'
set @.x1 = cast(cast(@.x1 as varbinary(max))+cast(@.x2 as varbinary(max)) as
xml)
select @.x1
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/|||thank you for your Help, Kent Tegels|||Alternatively:
select @.x1, @.x2 for xml path(''), type
Best regards
Michael
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad74179638c8dba6374e0e30@.news.microsoft.com...
> Hello sqlextreme,
> declare @.x1 xml,@.x2 xml
> set @.x1 = '<CAR name="1"/>'
> set @.x2 = '<CAR name="2"/>'
> set @.x1 = cast(cast(@.x1 as varbinary(max))+cast(@.x2 as varbinary(max)) as
> xml)
> select @.x1
> Thanks,
> Kent Tegels
> http://staff.develop.com/ktegels/
>

How can I concatenate fields XML in Yukon?

for example:
XML 1: <CAR name="1">
XML 2: <CAR name="2">
XML expected:
<CAR name="1">
<CAR name="2">
Thank.
Hello sqlextreme,
declare @.x1 xml,@.x2 xml
set @.x1 = '<CAR name="1"/>'
set @.x2 = '<CAR name="2"/>'
set @.x1 = cast(cast(@.x1 as varbinary(max))+cast(@.x2 as varbinary(max)) as
xml)
select @.x1
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
|||thank you for your Help, Kent Tegels
|||Alternatively:
select @.x1, @.x2 for xml path(''), type
Best regards
Michael
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad74179638c8dba6374e0e30@.news.microsoft.co m...
> Hello sqlextreme,
> declare @.x1 xml,@.x2 xml
> set @.x1 = '<CAR name="1"/>'
> set @.x2 = '<CAR name="2"/>'
> set @.x1 = cast(cast(@.x1 as varbinary(max))+cast(@.x2 as varbinary(max)) as
> xml)
> select @.x1
> Thanks,
> Kent Tegels
> http://staff.develop.com/ktegels/
>

How can i add a tool to SQL Server as a add-in(plug-in)

I have developed a tool which you can use to convert XML(DTD or XML Schema) to relational database model. I want to add it to SQL Server.

Can i do it?

If you are asking if you can install a plug-in to Management Studio (SSMS) the answer is no. Currently SSMS is locked down. For the next release of SQL Server we are investigating how to expose a full extensibility interface.

Cheers,
Dan