使用 XQuery 访问 SQLXML 列中的子节点

发布于 2024-11-09 09:45:12 字数 1248 浏览 4 评论 0原文

我有一个数据库，其中有一个带有 XML 列的表。 XML 数据有一堆子节点，看起来像这样：

<test>
  <result id="1234">
    <data elementname="Message">some error message</data>
    <data elementname="Cat">Cat01</data>
    <data elementname="Type">WARNING</data>
  </result>
  <result id="5678">
    <data elementname="Message">some error message</data>
    <data elementname="Cat">Cat01</data>
    <data elementname="Type">WARNING</data>
  </result>
</test>

Cat 元素可以有许多不同的值。我正在尝试创建有关此数据的报告，因此我想做的一件事是获取数据中所有类别的列表。这是我的查询：

Select Id, XmlData.query('/test/result/data[@elementname = ''Cat''] ')  AS  Message
From Table
WHERE XmlData.exist('/test/result/data[@elementname = ''Cat'']') = 1
ORDER BY FriendlyName

这可以正确获取表中具有这种类型分类的所有行（没有该元素的同一个表中将会有其他结果），但每个表记录的类别都合并为一列：

Id1, <data elementname="Cat">Cat01</data><data elementname="Cat">Cat01</data>
Id2, <data elementname="Cat">Cat01</data><data elementname="Cat">Cat01</data>

I包括 Id 列，这样很容易看到数据来自哪里，主要问题是我只能让它连接每行的值 - 我需要每个数据元素都有自己的行，然后也许做一个选择不同结果。

我有办法做到这一点吗？

谢谢

原文

I have a database which has a table with an XML column. The XML data has a bunch of child nodes which look something like this:

<test>
  <result id="1234">
    <data elementname="Message">some error message</data>
    <data elementname="Cat">Cat01</data>
    <data elementname="Type">WARNING</data>
  </result>
  <result id="5678">
    <data elementname="Message">some error message</data>
    <data elementname="Cat">Cat01</data>
    <data elementname="Type">WARNING</data>
  </result>
</test>

The Cat element can have a number of different values. I'm trying to create reports on this data, so one thing I'd like to do is get a list of all the categories througout our data. This is my query:

Select Id, XmlData.query('/test/result/data[@elementname = ''Cat''] ')  AS  Message
From Table
WHERE XmlData.exist('/test/result/data[@elementname = ''Cat'']') = 1
ORDER BY FriendlyName

This correctly gets all the rows in my table with this type of categorization (there'll be other results in the same table without that element), but the categories are all combined into one column for each table record:

Id1, <data elementname="Cat">Cat01</data><data elementname="Cat">Cat01</data>
Id2, <data elementname="Cat">Cat01</data><data elementname="Cat">Cat01</data>

I'm including the Id column so it's easy to see where the data is coming from, the main problem is that I can only get it to concatenate the values for each row - I need each of those data elements to have its own row, then maybe do a Select Distinct on the result.

Is there a way I can do that?

Thanks

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

强者自强 2024-11-16 09:45:12

在您发布问题之后，始终会出现 Google...

我想我在这里找到了答案：http://blogs.msdn.com/b/simonince/archive/2009/04/24/flattening-xml-data -in-sql-server.aspx

SELECT  DISTINCT  cref.value('(text())[1]', 'varchar(50)') as Cat
FROM   
      SGIS CROSS APPLY 
      Data.nodes('/test/result') AS Results(rref) CROSS APPLY
      rref.nodes('data[@elementname = ''Cat'']') AS Categories(cref)

看来关键是 Cross Apply 关键字

Always the Google after you post your question....

Think I found the answer here: http://blogs.msdn.com/b/simonince/archive/2009/04/24/flattening-xml-data-in-sql-server.aspx

SELECT  DISTINCT  cref.value('(text())[1]', 'varchar(50)') as Cat
FROM   
      SGIS CROSS APPLY 
      Data.nodes('/test/result') AS Results(rref) CROSS APPLY
      rref.nodes('data[@elementname = ''Cat'']') AS Categories(cref)

Seems the key is the Cross Apply keywords

回复收藏 0 原文

~没有更多了~