問題描述

要求解析與 MarginRevenue/Cost/Value 相關(guān)的所有 27 個(gè)值.作為標(biāo)準(zhǔn)方案，這些元素可以按任意順序出現(xiàn)，也可以出現(xiàn)任意次數(shù).簡而言之，XML 是完全動(dòng)態(tài)的.唯一的一點(diǎn)是 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>'

推薦答案

不太清楚您要實(shí)現(xiàn)的目標(biāo)/想要獲得的目標(biāo) - 但此 XQuery 將為您返回所有 27 個(gè)值:

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 中的結(jié)果:

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