T-Sql xml query with namespace

Question

This is the next question to

T-Sql xml query

If I add a namespace to the 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 can I select all elements in one element and return a row with all elements in the form of fields?

I want to return a record constructed as follows:

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

Answer 1

See 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) 

Answer 2

If my assumptions are correct and you want to list all ReportData elements in your XML document and want their children to be different columns, you can look 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 need to work a bit on namespace declarations, but it seems to work for me ...

EDIT: modified my answer with a WITH XMLNAMESPACES clause, as Martin recommended. :)