問題描述

我有一個如下所示的 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

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