T-Sql xml query with namespace

Question

This is a follow up question to

T-Sql xml query

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 |

Answer 1

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.