XML Parsing - SQL Server

问题:

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)

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:

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);

The result

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

回答2:

Actually, required result is not clear for me so please check below query for your required output and let me know if any changes required.

SELECT
[Margin_Revenue] = N.value('.', 'decimal(15,5)')
FROM
@xml.nodes('Margins/Margin/MarginRevenue') AS a(N)

回答3:

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