query XML while ignoring namespace?

Question

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

Help greatly appreciated.

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

I have edited your title. Please see, "Should questions include “tags” in their titles?", where the consensus is "no, they should not". — John Saunders
– John Saunders, Commented Jun 27, 2013 at 0:00
@SeanB.Durkin Unfortunately, SQL Server. Let me (re?)add the tag for that. — mbourgon
– mbourgon, Commented Jun 27, 2013 at 4:01
Your xml document does not match your expected output. For example ReportProc_aaaaa is not be be found anywhere in your xml. — Sean B. Durkin
– Sean B. Durkin, Commented Jun 27, 2013 at 4:27

Mikael Eriksson · Accepted Answer · 2013-06-27 06:26:20Z

22

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 Fiddle

edited Jun 27, 2013 at 6:26

answered Jun 27, 2013 at 6:17

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mbourgon Over a year ago

works like a charm! I figured I would need the cross apply, but couldn't figure out the colon syntax stuff. Much appreciated!

Sean B. Durkin · Accepted Answer · 2013-06-27 05:16:16Z

0

How about (untested) ....

select
    T.c.value(N'DataSourceName', N'nvarchar(100)') as DataSourceName,
    T.c.value(N'CommandType',N'nvarchar(100)') as CommandType,
    T.c.value(N'CommandText', N'nvarchar(100)') as CommandText
  from
    @myxml.nodes(N'/Report/DataSets/DataSet/Query') T(c)

edited Jun 27, 2013 at 5:16

answered Jun 27, 2013 at 5:06

Sean B. Durkin

12.8k2 gold badges39 silver badges73 bronze badges

1 Comment

mbourgon Over a year ago

FWIW, that didn't work - needed singletons. But IIRC I tried that way, and it didn't work because of the namespaces.

Collectives™ on Stack Overflow

query XML while ignoring namespace?

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related