Xquery help please (XML into SQL table)

Question

Post reply

Xquery help please (XML into SQL table)

Mr. Holio

Hall of Fame

Points: 3314

December 14, 2019 at 2:51 am

Go to Answer

#3707837

Hi everyone, any help with the below would be appreciated - see sample data and desired format below. The format of the xml records are not consistent unfortunately - is there a way to achieve the below? Thank you!!

declare @xml1 xml = '<Restrictions>
  <WeightLowerLimit>50</WeightLowerLimit>
  <WeightUpperLimit>60</WeightUpperLimit>
  <HeightLowerLimit>150</HeightLowerLimit>
  <HeightUpperLimit>250</HeightUpperLimit>
  <AgeLowerLimit>10</AgeLowerLimit>
  <AgeUpperLimit>100</AgeUpperLimit>
</Restrictions>'

declare @xml2 xml = '<Restrictions>
<Restrictions_a>
  <WeightLowerLimit>30</WeightLowerLimit>
  <WeightUpperLimit>60</WeightUpperLimit>
  <HeightLowerLimit>190</HeightLowerLimit>
  <HeightUpperLimit>260</HeightUpperLimit>
  <AgeLowerLimit>90</AgeLowerLimit>
  <AgeUpperLimit>95</AgeUpperLimit>
</Restrictions_a>
</Restrictions>'

declare @xml3 xml = '<Restrictions><AgeUpperLimit>95</AgeUpperLimit></Restrictions>'
declare @xml4 xml = '<Restrictions><AgeUpperLimit></AgeUpperLimit></Restrictions>'
declare @xml5 xml = NULL

declare @sampledata table(Id int identity(1,1) primary key, Category int not null, Restriction XML)
insert into @sampledata (Category, Restriction)
select 1, @xml1 union all
select 5, @xml2 union all
select 10, @xml3 union all
select 15, @xml4 union all
select 20, @xml5 

select * from @sampledata

--desired output

select 1 AS Id, 1 AS Category, 'WeightLowerLimit' AS LimitDescription, 50 AS LimitValue union all
select 1 AS Id, 1 AS Category, 'WeightUpperLimit' AS LimitDescription, 60 AS LimitValue union all
select 1 AS Id, 1 AS Category, 'HeightLowerLimit' AS LimitDescription, 150 AS LimitValue union all
select 1 AS Id, 1 AS Category, 'HeightUpperLimit' AS LimitDescription, 250 AS LimitValue union all
select 1 AS Id, 1 AS Category, 'AgeLowerLimit' AS LimitDescription, 10 AS LimitValue union all
select 1 AS Id, 1 AS Category, 'AgeUpperLimit' AS LimitDescription, 100 AS LimitValue union all

select 2 AS Id, 5 AS Category, 'WeightLowerLimit' AS LimitDescription, 30 AS LimitValue union all
select 2 AS Id, 5 AS Category, 'WeightUpperLimit' AS LimitDescription, 60 AS LimitValue union all
select 2 AS Id, 5 AS Category, 'HeightLowerLimit' AS LimitDescription, 190 AS LimitValue union all
select 2 AS Id, 5 AS Category, 'HeightUpperLimit' AS LimitDescription, 260 AS LimitValue union all
select 2 AS Id, 5 AS Category, 'AgeLowerLimit' AS LimitDescription, 90 AS LimitValue union all
select 2 AS Id, 5 AS Category, 'AgeUpperLimit' AS LimitDescription, 95 AS LimitValue union all

select 3 AS Id, 10 AS Category, 'AgeUpperLimit' AS LimitDescription, 95 AS LimitValue union all

select 4 AS Id, 15 AS Category, 'AgeUpperLimit' AS LimitDescription, NULL AS LimitValue union all

select 5 AS Id, 20 AS Category, NULL AS LimitDescription, NULL AS LimitValue

__________________________
Allzu viel ist ungesund...

Mark Cowne

One Orange Chip

Points: 26954
More actions
December 16, 2019 at 9:33 am
Answer
#3708075
If I understand this correctly, you want only leaf nodes
```
select s.Id,s.Category,
       x.n.value('local-name(.)','varchar(30)') as LimitDescription,
       x.n.value('./text()[1]','int') as LimitValue
from @sampledata s
outer apply s.Restriction.nodes('//*[not(child::*)]') x(n);
```
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80325 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.