为 SQL 表中的每一行粉碎 XML

2021-10-01 00:00:00 xml xpath sql tsql sql-server

我有一个包含两列、ID 和 XML 数据的表.我想为每个 ID 切碎 XML.我在 XML 中提取单个值，所有 XML 的结构都相同，我只是不确定如何遍历表并将 XML 查询应用于每一行.

I have a table that contains two columns, and ID, and XML data. I'd like to shred the XML for each ID. I'm pulling out a single value within the XML and all the XML is structured the same I'm just not sure how to loop through the table and apply XML query to each row.

我需要申请的查询如下:

The query I need to apply is as follows:

Select top 1
Element1 = XV.value('(.)[1]','nvarchar(32)')
from @xml.nodes('Parameters/Parameter/Value') as x(XV)

所以最终结果会有两列，ID 和来自 XML 的切碎值.

So the end results would have two columns, ID and shredded value from XML.

推荐答案

在不了解实际 XML 以及如何将其分解以获得某些值的情况下，无法完整地回答，但这应该指出正确的方向:

Without any knowledge about your actual XML and how you want to shred it to get some values it is impossible to answer in completness, but this shoudl point you in the right direction:

返回 ID 和 XML 原样

Returns the ID and the XML as is

 SELECT ID
       ,TheXmlColumn
 FROM YourTable

这会从您的 XML 中返回 ID 和值

This returns the ID and a value out of your XML

 SELECT ID
       ,TheXmlColumn.value('Some XPaht','SomeType') AS SomeValueFromXML
 FROM YourTable

如果有更多嵌入的行，它会是这样的

And if there are more embedded rows it would be something like this

 SELECT ID
       ,nd.value('Some XPaht','SomeType') AS SomeValueFromXMLRow
 FROM YourTable
 OUTER APPLY TheXmlColumn.nodes('SomeXPath') AS A(nd)

我的魔法玻璃灯泡告诉我，你可能需要这样的东西:

My magic glass bulb tells me, that you might need something like this:

 SELECT ID
       ,TheXmlColumn.value('(Parameters/Parameter/Value)[1]','nvarchar(max)') AS SomeValueFromXML
 FROM YourTable

相关文章