SQLXML -搜索和查询节点元素？

Question

我有这样的XML存储在XML数据类型列中(表中将有多个这样的行)-

<Root xmlns="http://tempuri.org" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Elem1 type="T1">
    <Name type="string" display="First name">John</Name>
    <TimeZone display="Time zone">
      <DisplayName type="string" display="Display name">GMT Standard Time</DisplayName>
    </TimeZone>
  </Elem1>
</Root> 

如何根据节点元素(使用SERVER 2008 R2)进行筛选-获取所有“Elem1”节点或获取所有“名称”节点或获取所有TimeZone节点？类似于使用本地名称()函数吗？

编辑- 部件解决方案-

我得到了部分的解决方案(见下面约翰的回答，然后运行这个)-

SELECT C1.query('fn:local-name(.)') AS Nodes FROM [dbo].[MyXmlTable] AS MyXML CROSS APPLY MyXML.MyXmlCol.nodes('//*') AS T ( C1 ) 

上面的查询返回跨表的所有节点元素。现在，我想说对特定元素进行筛选，并返回元素及其值或属性值。如何实现这一点(使用WHERE子句或任何其他筛选机制)？

Answer 1

我不知道你想要什么结果，但也许是这样的。

declare @T table(XMLCol xml)
insert into @T values
('<Root xmlns="http://tempuri.org" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Elem1 type="T1">
    <Name type="string" display="First name">John</Name>
    <TimeZone display="Time zone">
      <DisplayName type="string" display="Display name">GMT Standard Time</DisplayName>
    </TimeZone>
  </Elem1>
</Root>') 

declare @Node varchar(50)
set @Node = 'Elem1'

select N.query('.') as Value
from @T as T
  cross apply T.XMLCol.nodes('//*[local-name()=sql:variable("@Node")]') as X(N)

结果：

<p1:Elem1 xmlns:p1="http://tempuri.org" type="T1">
  <p1:Name type="string" display="First name">John</p1:Name>
  <p1:TimeZone display="Time zone">
    <p1:DisplayName type="string" display="Display name">GMT Standard Time</p1:DisplayName>
  </p1:TimeZone>
</p1:Elem1>

编辑

如果您想要的是实际值而不是整个XML，您可以这样做。

declare @Node varchar(50)
set @Node = 'TimeZone'

select N.value('.', 'varchar(100)') as Value
from @T as T
  cross apply T.XMLCol.nodes('//*[local-name()=sql:variable("@Node")]') as X(N)

结果：

Value
------------------
GMT Standard Time

Answer 2

可以将XML转换为表，如下所示：

  declare @XML xml='<Root xmlns="http://tempuri.org" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Elem1 type="T1">
    <Name type="string" display="First name">John</Name>
    <TimeZone display="Time zone">
      <DisplayName type="string" display="Display name">GMT Standard Time</DisplayName>
    </TimeZone>
  </Elem1>
</Root> '

;WITH XMLNAMESPACES(DEFAULT 'http://tempuri.org'),
numbers as(
SELECT  ROW_NUMBER() OVER(ORDER BY o1.object_id,o2.object_id) Num
FROM    sys.objects o1 CROSS JOIN  sys.objects o2),
 c as(
SELECT 
b.value('local-name(.)','nvarchar(1000)') Node_Name,
b.value('./text()[1]','nvarchar(1000)') Node_Value,
b.value('count(@*)','nvarchar(MAX)') AttributeCount,
Num Attribute_Number
FROM 
@xml.nodes('Root//*') a(b)
CROSS APPLY Numbers
WHERE Num<=b.value('count(@*)','nvarchar(MAX)')
)
SELECT c.Node_Name,c.node_Value,Attribute_Number,
    @XML.query('for $Attr in //*/.[local-name(.)=sql:column("Node_Name")]/@*[sql:column("Attribute_Number")] return local-name($Attr)').value('.','nvarchar(MAX)') Attribute_Name,
    @XML.value('data(//*/.[local-name(.)=sql:column("Node_Name")]/@*[sql:column("Attribute_Number")])[1]','nvarchar(1000)') Attribute_Value
FROM    c

结果：

Node_Name   node_Value          Attribute_Number    Attribute_Name  Attribute_Value
Elem1        NULL                       1              type             T1
Name         John                       1              type           string
Name         John                       2              display        First name
TimeZone     NULL                       1              display        Time zone
DisplayName GMT Standard Time           1              type            string
DisplayName GMT Standard Time           2              display     Display name

稍后，您可以查询此结果以获得所需的节点/属性值。

但它只适用于您的示例，当您只有一个节点并且所有名称都是唯一的时。在多项式XML中，您应该使用分层编号，比如'1-1-2‘或类似这样的东西。情况要复杂得多，我不建议这样做。

Answer 3

我不清楚你的输出应该是什么样子。但是，这应该会让您开始：

create table MyXmlTable (MyXmlCol xml)
insert into MyXmlTable (MyXmlCol) values 
(
'
<Root xmlns="http://tempuri.org" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Elem1 type="T1">
    <Name type="string" display="First name">John</Name>
    <TimeZone display="Time zone">
      <DisplayName type="string" display="Display name">GMT Standard Time</DisplayName>
    </TimeZone>
  </Elem1>
  <Elem1 type="T2">
    <Name type="string" display="First name">Fred</Name>
    <TimeZone display="Time zone">
      <DisplayName type="string" display="Display name">EST Standard Time</DisplayName>
    </TimeZone>
  </Elem1>
</Root> 
');

;WITH XMLNAMESPACES(DEFAULT 'http://tempuri.org')
select MyXmlCol.query('/Root/Elem1/Name')
from MyXmlTable

这将查询XML中的"Name“元素--您可以根据具体需要的输出类型来修改查询。它有点长，但关于SQLXML的MSDN文章提供了相当多的信息：

http://msdn.microsoft.com/en-us/library/ms345117(v=sql.90).aspx

希望这能有所帮助！

约翰

更新:您可以添加一个where子句，如下所示。我仍然不清楚您希望输出是什么样子，但这将筛选出"Elem1“值：

SELECT C1.query('fn:local-name(.)') AS Nodes 
FROM [dbo].[MyXmlTable] AS MyXML 
CROSS APPLY MyXML.MyXmlCol.nodes('//*') AS T ( C1 ) 
WHERE CAST(C1.query('fn:local-name(.)') AS NVARCHAR(32)) <> 'Elem1'

再来一次更新，希望这是你正在寻找的答案！

尝试在查询中使用通配符。我不得不使用动态SQL，因为XML query()函数将只对路径使用字符串文本(您可以对值使用sql:variable("@filter")，但我无法对路径使用字符串文本)。

DECLARE @filter nvarchar(20)
SET @filter = '*/Elem1'

DECLARE @sqlCommand nvarchar(1000)
SET @sqlCommand = 
    ';WITH XMLNAMESPACES(DEFAULT ''http://tempuri.org'')
    select MyXmlCol.query(''' + @filter + ''')
    from MyXmlTable'
print @sqlCommand
EXECUTE sp_executesql @sqlCommand, N'@filter nvarchar(20)', @filter = @filter

这将返回Elem1 XML (以及所有子节点)：

<p1:Elem1 xmlns:p1="http://tempuri.org" type="T1">
  <p1:Name type="string" display="First name">John</p1:Name>
  <p1:TimeZone display="Time zone">
    <p1:DisplayName type="string" display="Display name">GMT Standard Time</p1:DisplayName>
  </p1:TimeZone>
</p1:Elem1>
<p2:Elem1 xmlns:p2="http://tempuri.org" type="T2">
  <p2:Name type="string" display="First name">Fred</p2:Name>
  <p2:TimeZone display="Time zone">
    <p2:DisplayName type="string" display="Display name">EST Standard Time</p2:DisplayName>
  </p2:TimeZone>
</p2:Elem1>

如果你想选择"TimeZone“，你可以这样做：

SET @filter = '*/*/TimeZone'