T-Sql xml query with namespace

Question

This is a follow up question to

If I add a namespace to xml data, nothing is returned again.

DECLARE @xVar XML
SET @xVar = 
  '<ReportData ObjectId="123" xmlns="http://ait.com/reportdata">
  <ReportId>AAAA-BBBB-CCCCC-DDDDD</ReportId>
  <DocId>100</DocId>
  <ReportName>Drag Scraper Troubleshooting</ReportName>
  <DocType>Name</DocType>
  <StatusId>1</StatusId>
  <AuthorId>1</AuthorId>
   </ReportData>'

SELECT [ReportId]= reportdata.item.value('.', 'varchar(40)') 
FROM   @xVar.nodes('/ReportData/ReportId[1]') AS reportdata(item)

The above query returns nothing. Secondly, how would I select all elements in a single select and return a row with all elements as fields?

I want to return a record constructed as the following:

ReportId              | DocId | ReportName | 
AAAA-BBBB-CCCCC-DDDDD | 100   | AAAA-BBBB-CCCCC-DDDDD |

Could you maybe rephrase the question a bit? Assuming you have multiple ReportData elements, you want to return the elements as separate columns? Am I right in assuming that? — FarligOpptreden
– FarligOpptreden, Commented Jan 18, 2012 at 12:27

Martin Smith · Accepted Answer · 2012-01-18 12:37:04Z

17

Look at WITH XMLNAMESPACES

;WITH XMLNAMESPACES(DEFAULT 'http://ait.com/reportdata')
SELECT [ReportId]= reportdata.item.value('.', 'varchar(40)') 
FROM   @xVar.nodes('/ReportData/ReportId[1]') AS reportdata(item)

answered Jan 18, 2012 at 12:37

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

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

1 Comment

klashagelqvist Over a year ago

Thanks again, for those who are interested in final version: WITH XMLNAMESPACES(DEFAULT 'ait.com/reportdata') SELECT [ReportId]= reportdata.item.value('./ReportId[1]', 'varchar(40)') , [DocId]= reportdata.item.value('./DocId[1]', 'varchar(40)') FROM @xVar.nodes('/ReportData') AS reportdata(item). I wish i could give tribute to both answers her but not sure you can do that

Andrew Partridge · Accepted Answer · 2020-09-24 03:52:51Z

6

If you are trying to extract data and the namespace might differ between records, you can use a wildcard for the namespace prefix. Just put "*:" before each element name in the last line of the OP's original code, like this:

FROM   @xVar.nodes('/*:ReportData/*:ReportId[1]') AS reportdata(item)

Note that you need to use the wildcard at every level, not just at the same level as you see a namespace declaration in the xml. This is because namespaces are inherited by each level from the level above it.

answered Sep 24, 2020 at 3:52

Andrew Partridge

2112 silver badges1 bronze badge

1 Comment

Sotiris Zegiannis Over a year ago

you re a life saver!

FarligOpptreden · Accepted Answer · 2012-01-18 12:53:37Z

2

If my assumptions are correct and you want to list ALL ReportData elements in your XML document and want their child elements as different columns, you could look at something like this:

;WITH XMLNAMESPACES(DEFAULT 'http://ait.com/reportdata')
SELECT 
    [ReportId] = reportdata.item.value('(./ReportId)[1]', 'varchar(40)') 
  , [DocId] = reportdata.item.value('(./DocId)[1]', 'varchar(40)') 
  , [ReportName] = reportdata.item.value('(./ReportName)[1]', 'varchar(40)') 
  , [DocType] = reportdata.item.value('(./DocType)[1]', 'varchar(40)') 
  , [StatusId] = reportdata.item.value('(./StatusId)[1]', 'varchar(40)') 
  , [AuthorId] = reportdata.item.value('(./AuthorId)[1]', 'varchar(40)') 
FROM @xVar.nodes('//ReportData') AS reportdata(item)

I'll need to look at cleaning up the namespace declarations a bit, but it seems to work for me...

EDIT: Amended my answer with the WITH XMLNAMESPACES clause as recommended by Martin. :)

edited Jan 18, 2012 at 12:53

answered Jan 18, 2012 at 12:33

FarligOpptreden

5,04324 silver badges23 bronze badges

6 Comments

klashagelqvist Over a year ago

thanks , works for me too. If we only could get rid of those pesky namespace statements

klashagelqvist Over a year ago

I have seen there is a WITH XMLNAMESPACES statement , but i cant seem to get it to work

Martin Smith Over a year ago

@user1071785 - er, My answer has a working example with that (?)

FarligOpptreden Over a year ago

OK, amended my answer to include Martin's suggestion for the WITH XMLNAMESPACES clause... Hope it works out for you!

klashagelqvist Over a year ago

Thanks, i wish i could give tribute to both of you

|

Collectives™ on Stack Overflow

T-Sql xml query with namespace

3 Answers 3

1 Comment

1 Comment

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related