T-Sql xml query with namespace
Solution 1
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)
Solution 2
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.
Solution 3
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. :)
Related videos on Youtube
klashagelqvist
Updated on June 04, 2022Comments
-
klashagelqvist almost 2 years
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 |
-
FarligOpptreden over 12 yearsNot even an up-vote for my valid contribution? :(
-
-
klashagelqvist over 12 yearsthanks , works for me too. If we only could get rid of those pesky namespace statements
-
klashagelqvist over 12 yearsI have seen there is a WITH XMLNAMESPACES statement , but i cant seem to get it to work
-
Martin Smith over 12 years@user1071785 - er, My answer has a working example with that (?)
-
FarligOpptreden over 12 yearsOK, amended my answer to include Martin's suggestion for the WITH XMLNAMESPACES clause... Hope it works out for you!
-
klashagelqvist over 12 yearsThanks, i wish i could give tribute to both of you
-
Martin Smith over 12 years@klashagelqvist - You can! You can upvote helpful answers by clicking the up arrow to the left as well...