SQL Server 中的 XML 解析

2021-10-01 00:00:00 xml sql xml-parsing sql-server

要求解析与 MarginRevenue/Cost/Value 相关的所有 27 个值.作为标准方案，这些元素可以按任意顺序出现，也可以出现任意次数.简而言之，XML 是完全动态的.唯一的一点是 Under Margins/Margin，可以有任意数量的 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>'

推荐答案

不太清楚您要实现的目标/想要获得的目标 - 但此 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 中的结果:

相关文章