Extract attribute from XML in SQL Server

Question

Given this XML:

DECLARE @input XML = '<Data>
  <Tab Name="UserData">
    <Row>
      <BeneficialOwnerName>Fred Flintstone</BeneficialOwnerName>
      <TaxID>111-11-1111</TaxID>
    </Row>
    <Row>
      <BeneficialOwnerName>Barney Rubble</BeneficialOwnerName>
      <TaxID>222-22-2222</TaxID>
    </Row>
  </Tab>
  <Tab Name="OtherData">
    <Row>
      <BeneficialOwnerName>Bugs Bunny</BeneficialOwnerName>
      <TaxID>333-33-3333</TaxID>
    </Row>
    <Row>
      <BeneficialOwnerName>Road Runner</BeneficialOwnerName>
      <TaxID>444-44-4444</TaxID>
    </Row>
  </Tab>  
</Data>'

I'd like to return this:

Name     BeneficialOwnerName    TaxID
UserData  Fred Flintstone       111-11-1111
UserData  Barney Rubble         222-22-2222
OtherData Bugs Bunny            333-33-3333
OtherData Road Runner           444-44-4444

My SQL looks like this:

SELECT
  Name = XCol.value('@Name','varchar(25)'),   
  BeneficialOwnerName = XCol.value('BeneficialOwnerName[1]','varchar(25)'),
  TaxID = XCol.value('TaxID[1]','varchar(25)')
FROM 
  @input.nodes('/Data/Tab/Row') AS XTbl(XCol)

But depending on how I tweak the FROM clause I get either two rows of Tab names or 4 rows of data with the tab name Null

What do I need to do to get the data shown?

Thanks

Carl

This is a good question: copy-n_paste test code, expected output, own effort... +1 from my side — Gottfried Lesigang
– Gottfried Lesigang, Commented Jun 29, 2017 at 7:52

Roger Wolf · Accepted Answer · 2017-06-29 04:23:25Z

2

From the performance viewpoint, accessing parent element tends to be more expensive than selecting each level separately. The code below is slightly faster for your example, but will make a noticeable difference on a bigger XML:

SELECT
  Name = t.c.value('@Name','varchar(25)'),   
  BeneficialOwnerName = r.c.value('BeneficialOwnerName[1]','varchar(25)'),
  TaxID = r.c.value('TaxID[1]','varchar(25)')
FROM @input.nodes('/Data/Tab') t(c)
    cross apply t.c.nodes('./Row') r(c);

answered Jun 29, 2017 at 4:23

Roger Wolf

7,7872 gold badges29 silver badges37 bronze badges

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

2 Comments

Gottfried Lesigang Over a year ago

Good answer, +1 from my side, just one tiny hint: If you want the best performance, it is better to use (TaxID/text())[1] or - even slightly faster - TaxID[1]/text()[1]. You might read this answer why it is faster to use text().

Roger Wolf Over a year ago

@Shnugo, technically yes, and there is a difference between the two. However, even with the plans being different, the performance difference is negligible on this sample. One would need either a much bigger or a much more convoluted XML to notice this.

Dean Savović · Accepted Answer · 2017-06-28 20:41:46Z

1

Everything was fine except for Name column. Try this:

SELECT
  Name = XCol.value('../@Name','varchar(25)'),   
  BeneficialOwnerName = XCol.value('BeneficialOwnerName[1]','varchar(25)'),
  TaxID = XCol.value('TaxID[1]','varchar(25)')
FROM 
  @input.nodes('/Data/Tab/Row') AS XTbl(XCol)

answered Jun 28, 2017 at 20:41

Dean Savović

7393 silver badges7 bronze badges

Collectives™ on Stack Overflow

Extract attribute from XML in SQL Server

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related