問題描述
要求解析與 MarginRevenue/Cost/Value 相關的所有 27 個值.作為標準方案，這些元素可以按任意順序出現(xiàn)，也可以出現(xiàn)任意次數(shù).簡而言之，XML 是完全動態(tài)的.唯一的一點是 Under Margins/Margin，可以有任意數(shù)量的 MarginRevenue、MarginCost 和 MarginValue.
The requirement is to parse all the 27 values related to MarginRevenue/Cost/Value. As a standard scenario, these elements can come in any order and also can come in any number of time. In short, the XML is completely dynamic. The only point is Under Margins/Margin, there can be any number of MarginRevenue, MarginCost and MarginValue.
declare @xml xml = '<Margins>
<Margin type="type1" currencyCode="currencyCode1">
<MarginRevenue>1.1</MarginRevenue>
<MarginRevenue>1.2</MarginRevenue>
<MarginRevenue>1.3</MarginRevenue>
<MarginCost>2.1</MarginCost>
<MarginCost>2.2</MarginCost>
<MarginCost>2.3</MarginCost>
<MarginValue>3.1</MarginValue>
<MarginValue>3.2</MarginValue>
<MarginValue>3.3</MarginValue> </Margin>
<Margin type="type2" currencyCode="currencyCode2">
<MarginRevenue>1.4</MarginRevenue>
<MarginRevenue>1.5</MarginRevenue>
<MarginRevenue>1.6</MarginRevenue>
<MarginCost>2.4</MarginCost>
<MarginCost>2.5</MarginCost>
<MarginCost>2.6</MarginCost>
<MarginValue>3.4</MarginValue>
<MarginValue>3.5</MarginValue>
<MarginValue>3.6</MarginValue> </Margin>
<Margin type="type3" currencyCode="currencyCode3">
<MarginRevenue>1.7</MarginRevenue>
<MarginRevenue>1.8</MarginRevenue>
<MarginRevenue>1.9</MarginRevenue>
<MarginCost>2.7</MarginCost>
<MarginCost>2.8</MarginCost>
<MarginCost>2.9</MarginCost>
<MarginValue>3.7</MarginValue>
<MarginValue>3.8</MarginValue>
<MarginValue>3.9</MarginValue> </Margin>
</Margins>'
推薦答案
不太清楚您要實現(xiàn)的目標/想要獲得的目標 - 但此 XQuery 將為您返回所有 27 個值:
Not quite clear what you're trying to achieve / what you're looking to get - but this XQuery will return all 27 values for you:
SELECT
Name = XC.value('local-name(.)', 'varchar(100)'),
Value = XC.value('.', 'varchar(100)')
FROM
@xml.nodes('/Margins/Margin/*') AS XT(XC)
SSMS 中的結果:
這篇關于SQL Server 中的 XML 解析的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！