我试图在忽略命名空间的同时查询 XML,因为结果集有多个命名空间。我已经到达 DataSets 节点,但我不知道如何获取多个 DataSourceName/CommandType/CommandText。理想情况下我想要:
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
Use Nodes() 方法(xml 数据类型) http://msdn.microsoft.com/en-us/library/ms188282.aspx将 yoru XML 分解为行并使用value() 方法(xml 数据类型) http://msdn.microsoft.com/en-us/library/ms178030.aspx从 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小提琴 http://sqlfiddle.com/#!6/d41d8/4989
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)