|
Home > Archive > MS SQL XML > October 2006 > Moving XML Variable data to a SQL Table
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 |
Moving XML Variable data to a SQL Table
|
|
| Amos Soma 2006-10-28, 7:30 pm |
| Say I have the following T-SQL:
Declare @Doc xml
Declare @Table Table (ItemType nvarchar(10),UserTyp
e nvarchar(20), UserCount
int)
Set @Doc = '
<row ItemType="UserCount" UserType="Corporate" UserCount="0"/>
<row ItemType="UserCount" UserType="External Billable" UserCount="0"/>
<row ItemType="UserCount" UserType="External Non-Billable" UserCount="9"/>
<row ItemType="UserCount" UserType="Internal Billable" UserCount="71"/>
<row ItemType="UserCount" UserType="Internal Non-Billable" UserCount="0"/>
<row ItemType="UserCount" UserType="Leadership Center" UserCount="0"/>'
Can someone show me the T-SQL statement needed to take the 6 rows in the XML
variable @Doc and insert them into the Table variable @Table?
Thanks very much - Amos.
| |
| Kent Tegels 2006-10-28, 7:30 pm |
| "You have the following T-SQL"
Declare @Doc xml
Declare @Table Table (ItemType nvarchar(10),UserTyp
e nvarchar(20),UserCou
nt
int)
Set @Doc = '<row ItemType="UserCount" UserType="Corporate" UserCount="0"/><row
ItemType="UserCount" UserType="External Billable" UserCount="0"/><row ItemType="UserCount"
UserType="External Non-Billable" UserCount="9"/><row ItemType="UserCount"
UserType="Internal Billable" UserCount="71"/><row ItemType="UserCount" UserType="Internal
Non-Billable" UserCount="0"/> <row ItemType="UserCount" UserType="Leadership
Center" UserCount="0"/>'
insert into @table
select t.c. value('data(@ItemTyp
e)','nvarchar(10)'),
t.c. value('data(@UserTyp
e)','nvarchar(20)'),
t.c. value('data(@UserCou
nt)','int')
from @doc.nodes('row') as t(c)
select * from @table
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
| |
| Amos Soma 2006-10-31, 12:25 am |
| Thanks Kent. That is exactly what I was looking for.
"Kent Tegels" <ktegels@develop.com> wrote in message
news:b87ad74f4048c8c
82089568080@news.microsoft.com...
> "You have the following T-SQL"
>
> Declare @Doc xml
> Declare @Table Table (ItemType nvarchar(10),UserTyp
e
> nvarchar(20),UserCou
nt int)
> Set @Doc = '<row ItemType="UserCount" UserType="Corporate"
> UserCount="0"/><row ItemType="UserCount" UserType="External Billable"
> UserCount="0"/><row ItemType="UserCount" UserType="External Non-Billable"
> UserCount="9"/><row ItemType="UserCount" UserType="Internal Billable"
> UserCount="71"/><row ItemType="UserCount" UserType="Internal Non-Billable"
> UserCount="0"/> <row ItemType="UserCount" UserType="Leadership Center"
> UserCount="0"/>'
>
> insert into @table
> select t.c. value('data(@ItemTyp
e)','nvarchar(10)'),
> t.c. value('data(@UserTyp
e)','nvarchar(20)'),
> t.c. value('data(@UserCou
nt)','int')
> from @doc.nodes('row') as t(c)
>
> select * from @table
>
> Thanks,
> Kent Tegels
> http://staff.develop.com/ktegels/
>
>
|
|
|
|
|