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, May 23 '17 at 05: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, May 23 '17 at 06:04

Backs · Accepted Answer · 2017-05-23T15:00:49.590

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.

This is okay, but could be improved. ZLK's comment shows how... You might [read this](https://stackoverflow.com/a/43242238/5089204), 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 — Shnugo, May 23 '17 at 07:48
@Shnugo thank you for your comment, feel free to edit my answer, if you think, you can improve it — Backs, May 23 '17 at 15:00

Need help in retrieving XML data in SQL Server

1 Answers1