Xquery help please (XML into SQL table)

• Mr. Holio

  Hall of Fame

  Points: 3328

  More actions

  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...

• Site Owners

  SSC Guru

  Points: 80332

  More actions

  December 15, 2019 at 3:10 am

  #3707957

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

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

• Mark Cowne

  One Orange Chip

  Points: 26978

  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