This is a follow up question to
这是一个后续问题
t - sql xml查询
If I add a namespace to xml data, nothing is returned again.
如果我向xml数据添加一个名称空间,则不会再次返回任何内容。
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 |
2 个解决方案
#1
9
Look at WITH XMLNAMESPACES
看与XMLNAMESPACES
;WITH XMLNAMESPACES(DEFAULT 'http://ait.com/reportdata')
SELECT [ReportId]= reportdata.item.value('.', 'varchar(40)')
FROM @xVar.nodes('/ReportData/ReportId[1]') AS reportdata(item)
#2
1
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:
如果我的假设是正确的,并且您希望列出XML文档中的所有ReportData元素,并希望它们的子元素作为不同的列,您可以查看以下内容:
;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. :)
编辑:根据Martin推荐的xmlnamespace子句修改了我的答案。:)
#1
9
Look at WITH XMLNAMESPACES
看与XMLNAMESPACES
;WITH XMLNAMESPACES(DEFAULT 'http://ait.com/reportdata')
SELECT [ReportId]= reportdata.item.value('.', 'varchar(40)')
FROM @xVar.nodes('/ReportData/ReportId[1]') AS reportdata(item)
#2
1
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:
如果我的假设是正确的,并且您希望列出XML文档中的所有ReportData元素,并希望它们的子元素作为不同的列,您可以查看以下内容:
;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. :)
编辑:根据Martin推荐的xmlnamespace子句修改了我的答案。:)