CODEWITHSUNDEEP
sqlsql-serverxml
user2237168
user2237168

Reputation: 303

Inserting multiple child elements from XML into SQL Server database

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

Answers (1)

marc_s
marc_s

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

Related Questions