选择多个节点XML
问题描述:
我从检索字段NTEXT XML格式的文本(下面一排的样本格式):选择多个节点XML
<root>
<DocInfo>
<CompanyName>Some Company</CompanyName>
<WebsiteUrl>http://www.someurl.com</WebsiteUrl>
<PrimaryServices>Benefits Administration</PrimaryServices>
<PrimaryServices>Payroll Processing</PrimaryServices>
<SecondaryServices>Background Checking</SecondaryServices>
<SecondaryServices>HR Outsourcing</SecondaryServices>
<SecondaryServices>Comp & Benefits</SecondaryServices>
<SecondaryServices>Administration</SecondaryServices>
</DocInfo>
</root>
使用这个SQL,我检索了单个节点的值:
select @xmlString = COALESCE(@xmlString + '', '') + cast(content_html as nvarchar(max)) FROM content where folder_id = 18
set @xmlString = replace(@xmlString,'<?xml version="1.0" encoding="UTF-16" standalone="yes"?>','')
set @XML = cast(@xmlString as xml)
Select
T.N.value('CompanyName[1]', 'varchar(250)') as CompanyName,
T.N.value('WebsiteUrl[1]', 'varchar(250)') as WebsiteUrl,
T.N.value('PrimaryServices[1]', 'varchar(250)') as PrimaryServices,
T.N.value('SecondaryServices[1]', 'varchar(250)') as SecondaryServices,
T.N.value('Description[1]', 'varchar(max)') as Description
from @XML.nodes('/root/DocInfo') as T(N)
这适用于单节点值(CompanyName,WebsiteUrl)。但是,它没有正确插入具有多个值的节点(如PrimaryServices和SecondaryServices - 每个节点可能有0到16个节点)。如何将这些可变长度的多个节点值放入这些列中?
感谢所有帮助
答
要获得多个节点为逗号分隔值可以使用for xml path('')
招的变体。使用碎化的XML(TN)作为子查询中的源以获取您感兴趣的节点。xQuery ... substring(text()[1]) ...
部分就是为了删除额外的逗号并从创建的XML中获取逗号分隔值通过for xml
。
select
T.N.value('(CompanyName/text())[1]', 'varchar(250)') as CompanyName,
T.N.value('(WebsiteUrl/text())[1]', 'varchar(250)') as WebsiteUrl,
(
select ', '+P.N.value('text()[1]', 'varchar(max)')
from T.N.nodes('PrimaryServices') as P(N)
for xml path(''), type
).value('substring(text()[1], 2)', 'varchar(max)') as PrimaryServices,
(
select ', '+S.N.value('text()[1]', 'varchar(max)')
from T.N.nodes('SecondaryServices') as S(N)
for xml path(''), type
).value('substring(text()[1], 2)', 'varchar(max)') as SecondaryServices,
T.N.value('(Description/text())[1]', 'varchar(max)') as Description
from @XML.nodes('/root/DocInfo') as T(N)
如果您希望一列中的所有服务都可以在子查询中的节点部分使用不同的xPath。
select
T.N.value('(CompanyName/text())[1]', 'varchar(250)') as CompanyName,
T.N.value('(WebsiteUrl/text())[1]', 'varchar(250)') as WebsiteUrl,
(
select ', '+P.N.value('text()[1]', 'varchar(max)')
from T.N.nodes('PrimaryServices,SecondaryServices') as P(N)
for xml path(''), type
).value('substring(text()[1], 2)', 'varchar(max)') as Services,
T.N.value('(Description/text())[1]', 'varchar(max)') as Description
from @XML.nodes('/root/DocInfo') as T(N)
谢谢。这会得到xml和值。我如何获得价值? T – 2013-03-13 19:15:29
@ user971823你想要什么值?在一列中,单独的列,单独的行或其他....或? – 2013-03-13 19:35:07
单列逗号分隔将是理想的。 – 2013-03-13 20:02:46