問題描述

我有一個包含兩列、ID 和 XML 數(shù)據(jù)的表.我想為每個 ID 切碎 XML.我在 XML 中提取單個值，所有 XML 的結(jié)構(gòu)都相同，我只是不確定如何遍歷表并將 XML 查詢應(yīng)用于每一行.

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.

我需要申請的查詢?nèi)缦?

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)

所以最終結(jié)果會有兩列，ID 和來自 XML 的切碎值.

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

推薦答案

在不了解實際 XML 以及如何將其分解以獲得某些值的情況下，無法完整地回答，但這應(yīng)該指出正確的方向:

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

這篇關(guān)于為 SQL 表中的每一行粉碎 XML的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！