Parsing hochstrukturiertes XML in SQL server

Ich bin in das tiefe Ende ein wenig in Bezug auf SQLs XML-Parsing-functionalität geworfen.

Ich bin ein Stück XML übergeben, das ein bisschen so aussieht –

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

Und ich muss in der Lage sein, datasätze wie folgt zu extrahieren –

 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 

Ich kann einzelne Elemente mit solchen Befehlen extrahieren –

 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) 

Aber wo alles fällt, ist, dass ich nicht weiß, wie viele Fälle der AUNight und RatePrice Knoten wir wahrscheinlich bekommen werden. Ich weiß nicht, wie man SQL bekommt, um diese Ungewissheit zu behandeln und Details für alle Knoten herauszuziehen, egal wie viele es tatsächlich sind.

Ich habe versucht, variables anstelle von hartcodierten Zahlen / referenceen, aber die ganze Sache nur bläst und weigert sich zu laufen, wenn ich das tun. Irgendwelche Vorschläge?

 select scvalue('SubscriptionId[1]','nvarchar(50)') as SubscriptionId, acvalue('AunId[1]','nvarchar(50)') as AunId, acvalue('Night[1]','nvarchar(50)') as Night, acvalue('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 scnodes('Auns/AuNight') as a(c) outer apply acnodes('RatePrice/RatePrice') as rp(c)