Inserting multiple child elements from XML into SQL Server database

Question

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?

I just assumed that you wrote SQL but you really meant sql-server - the Microsoft relational database product (SQL is just the query language - not a product) — marc_s
– marc_s, Commented Dec 1, 2013 at 10:07

marc_s · Accepted Answer · 2013-12-01 10:04:58Z

2

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

answered Dec 1, 2013 at 10:04

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Inserting multiple child elements from XML into SQL Server database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related