XML解析-SQL Server

小编典典

XML解析-SQL Server

sql

我有一个如下所示的XML：

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>

‘

SELECT
[Margin_Revenue] = N.value('(MarginRevenue)[1]', 'decimal(15,5)')
,[Margin_Cost] = N.value('(MarginCost)[1]', 'decimal(15,5)')
,[Margin_Value] = N.value('(MarginValue)[1]', 'decimal(15,5)')
FROM
@xml.nodes('Margins/Margin') AS X(N)

我的要求是获取所有，但使用Path节点（’Margins / Margin’）AS
X（N）。截至目前，我仅获得以下记录，该记录实际上是每个保证金的第一条记录：

    Margin_Revenue  Margin_Cost  Margin_Value
    1.10000         2.10000      3.10000
    1.40000         2.40000      3.40000
    1.70000         2.70000      3.70000

阅读 189

2021-04-22

共1个答案

小编典典

处有1:n数据，处<Margin>又有1:n数据<MarginRevenue>。您需要通过.nodes()两次使用APPLY。

declare @xml xml = '<Margins>
    <Margin type="type1" currencyCode="currencyCode1">
      <MarginRevenue>1.1</MarginRevenue>
      <MarginRevenue>1.2</MarginRevenue>
      <MarginRevenue>1.3</MarginRevenue>
    </Margin>
    <Margin type="type2" currencyCode="currencyCode2">
      <MarginRevenue>1.4</MarginRevenue>
      <MarginRevenue>1.5</MarginRevenue>
      <MarginRevenue>1.6</MarginRevenue>
    </Margin>
    <Margin type="type3" currencyCode="currencyCode3">
      <MarginRevenue>1.7</MarginRevenue>
      <MarginRevenue>1.8</MarginRevenue>
      <MarginRevenue>1.9</MarginRevenue>
      </Margin>
    </Margins>'

SELECT
 [Margin_Type]         = Marg.value('@type', 'varchar(100)') 
,[Margin_currencyCode] = Marg.value('@currencyCode', 'varchar(100)')
,[Revenue_Value]       = Rev.value('text()[1]','decimal(15,5)') 
FROM
@xml.nodes('Margins/Margin') AS A(Marg)
OUTER APPLY Marg.nodes('MarginRevenue') B(Rev);

结果

Type    currencyCode    Revenue_Value
-------------------------------------
type1   currencyCode1   1.10000
type1   currencyCode1   1.20000
type1   currencyCode1   1.30000
type2   currencyCode2   1.40000
type2   currencyCode2   1.50000
type2   currencyCode2   1.60000
type3   currencyCode3   1.70000
type3   currencyCode3   1.80000
type3   currencyCode3   1.90000

2021-04-22