XML Parsing - SQL Server

Question

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

adding a desired output will help more.

maulik kansara
– maulik kansara

2018-07-31 09:52:14 +00:00
Commented Jul 31, 2018 at 9:52 — maulik kansara
– maulik kansara, Commented Jul 31, 2018 at 9:52

Gottfried Lesigang · Accepted Answer · 2018-07-31 11:14:07Z

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

answered Jul 31, 2018 at 11:14

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

AG7 Over a year ago

I have updated my question, please have a look. Thanks a lot:)

Gottfried Lesigang Over a year ago

@Ajai please avoid chameleon questions... Your "update" is a completely new issue... What you should do instead: Rollback your update and close this question (the initial question seems to be answered) by accepting the best answer and vote on all contributions you like or dislike. Then start a new question and place a link here. Continues...

Gottfried Lesigang Over a year ago

@Ajai ... continuing... Start the new qeustion and explain, whether there are always exactly three elements or if their count may vary. Is the only linkage their ordinal position (second rev pairs with second cost and second value)? Might there be missing values or NULL values? How are they linked in such cases? Is there a maximum count of triples? And: If this XML is under your control, you might ask, how to find a better structure...

AG7 Over a year ago

Apologies for the inconvenienced caused to you Shnugo. Pls allow me answer your queries. 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. Hope I'm clear now.

Gottfried Lesigang Over a year ago

@AG7 Above you state the expected output. You provide a list with Revenue, Cost and Value in one row. The only chance to do this, is to place the first Revenue, the first Cost and the first Value in one row, the same with all second values and so on. The only way to "map" these elements is their position. What does any number mean? What, if there are 5 Revenues, but 3 Costs ans 4 Values? Did you check my answer at your second question?

|

maulik kansara · Accepted Answer · 2018-07-31 10:03:29Z

1

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)

answered Jul 31, 2018 at 10:03

maulik kansara

1,1276 silver badges21 bronze badges

1 Comment

AG7 Over a year ago

Actually the reason why I mentioned the Path

Vinutha N · Accepted Answer · 2018-07-31 10:13:03Z

0

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

answered Jul 31, 2018 at 10:13

Vinutha N

1666 bronze badges

Collectives™ on Stack Overflow

XML Parsing - SQL Server

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related