来自@xml.nodes SQL xml

Question

想知道有没有人能帮忙。我有一个xml字符串，它看起来像这样。

<install>
  <client>
    <account_id><![CDATA[123]]></account_id>
<account_name><![CDATA[123]]></account_name>
<account_name_abbr><![CDATA[123]]></account_name_abbr>
<location>
  <id><![CDATA[5]]></id>
  <name><![CDATA[Bryanston Lab]]></name>
</location>
<ram><![CDATA[10]]></ram>
<cpu><![CDATA[20]]></cpu>
<disk><![CDATA[30]]></disk>
<ip_ranges>
  <item><![CDATA[1.1.1.1/27]]></item>
  <item><![CDATA[1.1.1.1/27]]></item>
  <item><![CDATA[2.2.2.2/25]]></item>
</ip_ranges>
<vlans>
  <item><![CDATA[4001]]></item>
  <item><![CDATA[3000]]></item>
  <item><![CDATA[350]]></item>
</vlans>
</client>
</install>

我使用以下方法插入数据

insert into tbl_users
select
x.y.value('(account_id)[1]', 'nvarchar(150)') as account_id
,x.y.value('(account_name)[1]', 'nvarchar(350)') as account_name
,x.y.value('(account_name_abbr)[1]', 'nvarchar(150)') as account_name_abbr
,x.y.value('(location/id)[1]', 'int') as location_id
,x.y.value('(cpu)[1]', 'int') as cpu_amount
,x.y.value('(ram)[1]', 'int') as ram_amount
,x.y.value('(disk)[1]', 'int') as disk_amount
from @xml.nodes('//install/client') as x(y)

我的问题是，一旦初始插入完成，我想插入所有的vlans项目，但我不知道如何循环它们并获得正确的数据。

有人能帮帮我吗。

谢谢你，杰布

Answer 1

如果tbl_users中的PK字段是自动递增的，则可以使用如下所示：

insert into tbl_users
select
x.y.value('(account_id)[1]', 'nvarchar(150)') as account_id
,x.y.value('(account_name)[1]', 'nvarchar(350)') as account_name
,x.y.value('(account_name_abbr)[1]', 'nvarchar(150)') as account_name_abbr
,x.y.value('(location/id)[1]', 'int') as location_id
,x.y.value('(cpu)[1]', 'int') as cpu_amount
,x.y.value('(ram)[1]', 'int') as ram_amount
,x.y.value('(disk)[1]', 'int') as disk_amount
from @xml.nodes('//install/client/vlans') as x(y)
declare @tbl_users_id int = scope_identity()

insert tbl_users_vlans([user_id],[vlan])
select @tbl_users_id, x.y.value('data(.)', 'nvarchar(150)')
from @xml.nodes('//install/client/vlans/item') as x(y)

如果需要的话，您也可以在事务中这样做。