CODEWITHSUNDEEP
sql-serverxmlparsingxpathsqlxml
Chris
Chris

Reputation: 11

Parsing highly structured XML in SQL server

I'm being thrown in at the deep end a little with regard to SQLs XML parsing functionality.

I'm being passed a piece of XML that looks a little like this-

<AuSale>
  <SubscriptionId>d5a996c0</SubscriptionId>
  <Auns>
    <AuNight>
      <AunId>00000000-0000</AunId>
      <Night>2014-10-23T00:00:00</Night>
      <AuId>4b8ca8db</AuId>
      <State>Booked</State>
      <RatePrice>
        <RatePrice>
          <AunId>00000000-0000</AunId>
          <Id>2143124</Id>
          <Price>565665</Price>
        </RatePrice>
        <RatePrice>
          <AunId>00000000-0000</AunId>
          <Id>jtty54</Id>
          <Price>65383</Price>
        </RatePrice>
      </RatePrice>
    </AuNight>
    <AuNight>
      <AunId>00000000-0000</AunId>
      <Night>2014-10-24T00:00:00</Night>
      <AuId>4b8ca8db</AuId>
      <State>Booked</State>
      <RatePrice>
        <RatePrice>
          <AunId>00000000-0000</AunId>
          <Id>2143124</Id>
          <Price>565665</Price>
        </RatePrice>
      </RatePrice>
    </AuNight>
  </Auns>
</AuSale>

And I need to be able to extract datasets like this from it-

SubscriptionId  AunId       Night               AuId        State   Id      Price
-------------------------------------------------------------------------------------
d5a996c0    00000000-0000   2014-10-23T00:00:00 4b8ca8db    Booked  2143124 565665
d5a996c0    00000000-0000   2014-10-23T00:00:00 4b8ca8db    Booked  jtty54  65383
d5a996c0    00000000-0000   2014-10-24T00:00:00 4b8ca8db    Booked  2143124 565665

I can extract individual elements using commands like this-

SELECT 
bookref.ent.value('SubscriptionId[1]','nvarchar(50)') as 'subid',
bookref.ent.value('(Auns/AuNight/AunId)[1]','nvarchar(50)') as 'nightid',
bookref.ent.value('(Auns/AuNight/AunId)[1]','nvarchar(50)') as 'nightid',
bookref.ent.value('(Auns/AuNight/RatePrice/RatePrice/Price)[1]','nvarchar(50)') as 'nightid',
bookref.ent.value('(Auns/AuNight/RatePrice/RatePrice/Price)[2]','nvarchar(50)') as 'nightid2'
FROM @testxml.nodes('/AuSale') bookref(ent)

But where it all falls down is that I don't know how many instances of the AUNight and RatePrice node we're likely to get. I don't know how to get SQL to handle that uncertainty and pull out details for all the nodes, no matter how many there actually are.

I've tried using variables instead of hardcoded numbers/ references, but the entire thing just blows up and refuses to run if I do that. Any suggestions?

Upvotes: 1

Views: 36

Answers (1)

roman
roman

Reputation: 117400

select
    s.c.value('SubscriptionId[1]','nvarchar(50)') as SubscriptionId,
    a.c.value('AunId[1]','nvarchar(50)') as AunId,
    a.c.value('Night[1]','nvarchar(50)') as Night,
    a.c.value('State[1]','nvarchar(50)') as [State],
    rp.c.value('Id[1]','nvarchar(50)') as Id,
    rp.c.value('Price[1]','nvarchar(50)') as Price
from @data.nodes('AuSale') as s(c)
    outer apply s.c.nodes('Auns/AuNight') as a(c)
    outer apply a.c.nodes('RatePrice/RatePrice') as rp(c)

Upvotes: 1

Related Questions