在忽略命名空间的同时查询 XML? [英] query XML while ignoring namespace?

问题描述

我试图在忽略命名空间的同时查询 XML，因为结果集有多个命名空间.我已经到了 DataSets 节点，但我不知道如何取出多个 DataSourceName/CommandType/CommandText.理想情况下，我想要:

I'm trying to query XML while ignoring namespaces, because the result set has multiple namespaces. I've gotten to the DataSets node, but I can't figure out how to get out the multiple DataSourceName/CommandType/CommandText. Ideally I want:

DataSetName   DataSourceName   CommandType      CommandText
SQLDS         SQLDS            StoredProcedure  ReportProc_aaaaa
SQLDS         SQLDS            StoredProcedure  ReportProc_lalala

非常感谢帮助.

DECLARE @xmltable TABLE (myxml XML)
INSERT INTO @xmltable 
SELECT   
'<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
  <DataSources>
    <DataSource Name="SQLDS">
      <rd:DataSourceID>32e83b35-434d-4808-b685-ada14accd0e7</rd:DataSourceID>
      <DataSourceReference>SQLDS</DataSourceReference>
    </DataSource>
  </DataSources>
  <DataSets>
    <DataSet Name="SQLDS">
      <Query>
        <DataSourceName>SQLDS</DataSourceName>
        <CommandType>StoredProcedure</CommandType>
        <CommandText>ReportProc_ServerPerformanceGroup</CommandText>
      </Query>
    </DataSet>
    <DataSet Name="GroupDetails">
      <Query>
        <DataSourceName>SQLDS</DataSourceName>
        <CommandType>StoredProcedure</CommandType>
        <CommandText>ReportProc_lalala</CommandText>
      </Query>
    </DataSet>
  </DataSets>
</Report>'

SELECT myxml.value('(/*:Report/*:DataSets)[1]','varchar(100)') FROM @xmltable

推荐答案

使用 nodes() 方法(xml 数据类型) 将 yoru XML 分解为行并使用 value() 方法(xml 数据类型) 从 XML 中获取特定值.

Use nodes() Method (xml Data Type) to shred yoru XML to rows and use value() Method (xml Data Type) to get specific values from the XML.

select T1.N.value('@Name', 'nvarchar(128)') as DataSetName,
       T2.N.value('(*:DataSourceName/text())[1]', 'nvarchar(128)') as DataSourceName,
       T2.N.value('(*:CommandType/text())[1]', 'nvarchar(128)') as CommandType,
       T2.N.value('(*:CommandText/text())[1]', 'nvarchar(max)') as CommandText
from @xmltable as T
  cross apply T.myxml.nodes('/*:Report/*:DataSets/*:DataSet') as T1(N)
  cross apply T1.N.nodes('*:Query') as T2(N)

SQL 小提琴

这篇关于在忽略命名空间的同时查询 XML?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！