Need help in retrieving XML data in SQL Server

Question

I need help in retrieving the data from this XML that is stored in a column in a SQL Server table. I've been searching the questions repository but couldn't find any that matches mine. Maybe i missed out somehow. Anyway, here how the stored XML looks like :

<ProductStructure>
  <Plans>Essential</Plans>
  <Plans>Standard</Plans>
  <Plans>Silver</Plans>
  <Plans>Gold</Plans>
  <Plans>Platinum</Plans>
  <Plans>Titanium</Plans>
  <Destinations>Region A</Destinations>
  <Destinations>Region B</Destinations>
  <Destinations>Region C</Destinations>
  <Destinations>Region D</Destinations>
  <InsuredTypes>One Person</InsuredTypes>
  <InsuredTypes>Couple</InsuredTypes>
  <InsuredTypes>Group</InsuredTypes>
</ProductStructure>

I would like to select the xml and get an output like below in SQL:

|Plans          |Essential    |
|               |Standard     |
|               |Silver       |
|               |Gold         |
|               |Platinum     |
|               |Titanium     |
|---------------+-------------|
|Destinations   |Region A     |
|               |Region B     |
|               |Region C     |
|               |Region D     |
|---------------+-------------|
|InsuredTypes   |One Person   |
|               |Couple       |
|               |Group        |

Sorry for if the question was visualized badly. Thank you.

Something like this would work for your data: SELECT Field = A.B.value('local-name(.)', 'VARCHAR(255)'), Val = A.B.value('text()[1]', 'VARCHAR(255)') FROM @XML.nodes('ProductStructure/*') AS A(B); — ZLK
– ZLK, Commented May 23, 2017 at 5:53
@ZLK holy smokes. You made it looks like child's play. Thank you good sir. It worked. You really saved the hassle for me. — Shah
– Shah, Commented May 23, 2017 at 6:04

Backs · Accepted Answer · 2017-05-23 15:00:49Z

2

DECLARE @xml xml = N'<ProductStructure>
  <Plans>Essential</Plans>
  <Plans>Standard</Plans>
  <Plans>Silver</Plans>
  <Plans>Gold</Plans>
  <Plans>Platinum</Plans>
  <Plans>Titanium</Plans>
  <Destinations>Region A</Destinations>
  <Destinations>Region B</Destinations>
  <Destinations>Region C</Destinations>
  <Destinations>Region D</Destinations>
  <InsuredTypes>One Person</InsuredTypes>
  <InsuredTypes>Couple</InsuredTypes>
  <InsuredTypes>Group</InsuredTypes>
</ProductStructure>'

SELECT
    t.value('local-name(.)','nvarchar(max)'),
    t.value('.','nvarchar(max)')
FROM @xml.nodes('ProductStructure/*') AS t(t)

And you'll get:

-------------------- --------------------
Plans                Essential
Plans                Standard
Plans                Silver
Plans                Gold
Plans                Platinum
Plans                Titanium
Destinations         Region A
Destinations         Region B
Destinations         Region C
Destinations         Region D
InsuredTypes         One Person
InsuredTypes         Couple
InsuredTypes         Group

You can group results as you want.

edited May 23, 2017 at 15:00

answered May 23, 2017 at 6:03

Backs

25k5 gold badges66 silver badges92 bronze badges

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

3 Comments

Shah Over a year ago

Thank you sir. It works. I really appreciate your answer.

Gottfried Lesigang Over a year ago

This is okay, but could be improved. ZLK's comment shows how... You might read this, why .value(N'text()[1]',...) or .value(N'(./text())[1]',...) is better than just .value('.',...). Furthermore it is recommended to be as specific as possible. So .nodes(N'/ProductStructure/*') is better than .nodes('*/*'). Anyway, good answer! +1 from my side

Backs Over a year ago

@Shnugo thank you for your comment, feel free to edit my answer, if you think, you can improve it

Collectives™ on Stack Overflow

Need help in retrieving XML data in SQL Server

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related