Reputation: 303
The following XML is declared:
DECLARE @XML XML = '<reservering reserveringsnummer="1" status="aanvraag">
<park>Nationaal park Oosterschelde</park>
<accomodatie>Appartement</accomodatie>
<aantalBezoekers>2</aantalBezoekers>
<startdatum>2013-12-12</startdatum>
<einddatum>2013-12-20</einddatum>
<voorkeur>Rustige plaats</voorkeur>
<opties>
<optie>arrangement</optie>
<optie>bedlinnenset</optie>
</opties>
<klant>
<aanhef>De heer</aanhef>
<naam>Naam</naam>
<adres>Adres</adres>
<postcode>Postcode</postcode>
<email>Mail</email>
<rekeningnummer>nummer</rekeningnummer>
</klant>
</reservering>'
I want to insert the options (arrangement and bedlinnenset) into my database table. Right now I have the following code to achieve this:
INSERT INTO [RESERVERING_PRODUCT](
[reserveringnummer], [product])
SELECT
ReserveringNummer = Reservering.value('@reserveringsnummer', 'int'),
Opties = Opties.value('(optie)[1]', 'varchar(50)')
FROM
@XML.nodes('/reservering') AS ReserveringTable(Reservering),
@XML.nodes('/reservering/opties') AS OptiesTable(Opties)
With this code I can only choose which option has to be inserted in my table by doing (optie)[1]
, (optie)[2]
etc. But how can I insert every option which is in my XML?
Upvotes: 1
Views: 1197
Reputation: 755541
You're pretty close - you just need to add a CROSS APPLY
to get a list of all <optie>
subnodes under your <reservering>
node:
SELECT
ReserveringNummer = XReservering.value('@reserveringsnummer', 'int'),
Opties = XOpties.value('(.)', 'varchar(50)')
FROM
@XML.nodes('/reservering') AS XTbl1(XReservering)
CROSS APPLY
XReservering.nodes('opties/optie') AS XTbl2(XOpties)
Once you do that - you get all your <optie>
nodes evaluated and now you can easily insert that data into your table
Upvotes: 2