Xquery help please (XML into SQL table)

  • Mr. Holio

    Hall of Fame

    Points: 3314

    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: 80376

    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: 26752

    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 3 (of 3 total)

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