T-Sql带有命名空间的xml查询

Question

这是一个关于

T-Sql xml query

的后续问题。如果我给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) 

以上查询没有返回任何结果。其次，我如何选择单个select中的所有元素并将所有元素作为字段返回一行记录？

我想返回一个记录，构造如下：

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

- klashagelqvist

---

你能否稍微改一下问题的措辞？假设你有多个ReportData元素，你想将这些元素作为单独的列返回？我猜测是这样吗？ - FarligOpptreden

稍微修改了一下我的问题，希望有所帮助。 - klashagelqvist

我的有价值的贡献连个赞都没有吗？ :( - FarligOpptreden

Answer 1

请查看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) 

- Martin Smith

---

再次感谢对最终版本感兴趣的人：WITH XMLNAMESPACES(DEFAULT 'http://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)。我希望能够向两个答案都致敬，但不确定您是否可以这样做。 - klashagelqvist

Answer 2

如果我的假设是正确的，你想要罗列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)

我需要查看一下命名空间声明的清理工作，但对我来说似乎可以正常工作...

编辑：根据Martin的建议，使用了WITH XMLNAMESPACES子句修改了我的答案。 :)

- FarligOpptreden

---

谢谢，对我也有效。如果我们能摆脱那些讨厌的命名空间声明就好了。 - klashagelqvist

我看到了WITH XMLNAMESPACES语句，但似乎无法使其工作。 - klashagelqvist

@user1071785 - 嗯，我的答案中有一个带有那个的工作示例（？） - Martin Smith

好的，我修改了我的答案，加入了Martin提出的WITH XMLNAMESPACES子句建议...希望对你有帮助！ - FarligOpptreden

谢谢，我希望我能向你们两个致敬。 - klashagelqvist

@klashagelqvist - 你可以的！你可以通过点击左侧的上箭头来为有帮助的答案点赞。 - Martin Smith

Answer 3

如果您正在尝试提取数据，而记录之间的命名空间可能不同，那么您可以在命名空间前缀中使用通配符。只需在OP原始代码的最后一行的每个元素名称前加上“*：”即可，如下所示：

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

请注意，您需要在每个级别上都使用通配符，而不仅仅是在 XML 中看到命名空间声明的相同级别上。这是因为命名空间从上一级别继承到每个级别下面。

- Andrew Partridge