Shah Shah - 1 year ago 32
SQL Question

Need help in retrieving XML data in MSSQL

I need help in retrieving the data from this XML that is stored in a column in MSSQL DB 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.

Answer Source
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('*/*') 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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download