Moving XML Variable data to a SQL Table

Say I have the following T-SQL:
Declare @.Doc xml
Declare @.Table Table (ItemType nvarchar(10),UserType 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."You have the following T-SQL"
Declare @.Doc xml
Declare @.Table Table (ItemType nvarchar(10),UserType nvarchar(20),UserCount
int)
Set @.Doc = '<row ItemType="UserCount" UserType="Corporate" UserCount="0"/><r
ow
ItemType="UserCount" UserType="External Billable" UserCount="0"/><row ItemTy
pe="UserCount"
UserType="External Non-Billable" UserCount="9"/><row ItemType="UserCount"
UserType="Internal Billable" UserCount="71"/><row ItemType="UserCount" UserT
ype="Internal
Non-Billable" UserCount="0"/> <row ItemType="UserCount" UserType="Leadership
Center" UserCount="0"/>'
insert into @.table
select t.c. value('data(@.ItemType)','nvarchar(10)'),
t.c. value('data(@.UserType)','nvarchar(20)'),
t.c.value('data(@.UserCount)','int')
from @.doc.nodes('row') as t(c)
select * from @.table
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/|||Thanks Kent. That is exactly what I was looking for.
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad74f4048c8c82089568080@.news.microsoft.com...
> "You have the following T-SQL"
> Declare @.Doc xml
> Declare @.Table Table (ItemType nvarchar(10),UserType
> 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"/>'
> insert into @.table
> select t.c. value('data(@.ItemType)','nvarchar(10)'),
> t.c. value('data(@.UserType)','nvarchar(20)'),
> t.c.value('data(@.UserCount)','int')
> from @.doc.nodes('row') as t(c)
> select * from @.table
> Thanks,
> Kent Tegels
> http://staff.develop.com/ktegels/
>