XML 解析 - SQL Server

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

我有一个如下所示的 XML:

I have an XML that looks like below:

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 nodes('Margins/Margin') AS X(N).到目前为止，我只得到下面这实际上是每个 Margin 的第一条记录:

My requirement is to get all the , and but with Path nodes('Margins/Margin') AS X(N). As of now, I'm getting below only which is actually the first record of each Margin:

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

推荐答案

和 1:n 处有 1:n 数据 数据位于 .您需要通过 APPLY 使用 .nodes() 两次.

There is 1:n data at <Margin> and again 1:n data at <MarginRevenue>. You need to use .nodes() twice via 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

相关文章