Inserting XML data in SQL database

Question

How can i get my XML data into the right tables in my database? I have the following code:

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>
</opties>
<klant>
    <aanhef>De heer</aanhef>
    <naam>voornaam</naam>
    <adres>adres</adres>
    <postcode>postcode</postcode>
    <email>[email protected]</email>
    <rekeningnummer>012345678</rekeningnummer>
</klant>
</reservering>'

'SELECT
ReserveringNummer = Reservering.value('@reserveringsnummer', 'int'),
ReserveringStatus = Reservering.value('@status', 'varchar(50)'),
Park = Reservering.value('(park)[1]', 'varchar(50)'),
Accomodatie = Reservering.value('(accomodatie)[1]', 'varchar(50)'),
AantalBezoekers = Reservering.value('(aantalBezoekers)[1]', 'int'),
StartDatum = Reservering.value('(startdatum)[1]', 'date'),
EindDatum = Reservering.value('(einddatum)[1]', 'date'),
Voorkeur = Reservering.value('(voorkeur)[1]', 'varchar(50)'),
Opties = Opties.value('(optie)[1]', 'varchar(50)'),
Aanhef = Klant.value('(aanhef)[1]', 'varchar(50)'),
KlantNaam = Klant.value('(naam)[1]', 'varchar(50)'),
Adres = Klant.value('(adres)[1]', 'varchar(50)'),
Postcode = Klant.value('(postcode)[1]', 'varchar(50)'),
Email = Klant.value('(email)[1]', 'varchar(50)'),
Rekeningnummer = Klant.value('(rekeningnummer)[1]', 'varchar(50)')
FROM
@XML.nodes('/reservering') AS ReserveringTable(Reservering),
@XML.nodes('/reservering/opties') AS OptiesTable(Opties),
@XML.nodes('/reservering/klant') AS KlantTable(Klant)

INSERT INTO dbo.PRODUCT(naam)
  VALUES (Opties)
SELECT SCOPE_IDENTITY() AS ProductID;

INSERT INTO dbo.WONING(naam)
  VALUES (Accomodatie)
SELECT SCOPE_IDENTITY() AS WoningID;

INSERT dbo.KLANT(aanhef, naam, adres, postcode, emailadres, rekeningnummer)
  VALUES (Aanhef, KlantNaam, Adres, Postcode, Email, Rekeningnummer)
SELECT SCOPE_IDENTITY() AS KlantID;

INSERT INTO dbo.RESERVERING(reserveringsnummer, status, van_datum, tot_datum,       woning_id, product, voorkeur, prijs, klant, aantal_bezoekers)
   VALUES (ReserveringNummer, ReserveringStatus, StartDatum, EindDatum, WoningID, ProductID, Voorkeur, Prijs, KlantID, AantalBezoekers)

As you can see i'm also trying to get the product and woning id's into my RESERVERING table, is this the right way to do this? Right now the program is giving me an error: invalid column name 'columnname', where columnname is the value. It gives me the same error for all the values i've defined in my INSERT statement.

SQL Server 2008 R2 and i'm working with the Microsoft SQL Server Management Studio — user2237168
– user2237168, Commented Nov 29, 2013 at 21:13

user2237168 · Accepted Answer · 2013-11-29 23:51:03Z

Fixed the problem. For people who are interested in the solution:

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>
</opties>
<klant>
    <aanhef>De heer</aanhef>
    <naam>name</naam>
    <adres>adres</adres>
    <postcode>postcode</postcode>
    <email>mail</email>
    <rekeningnummer>012345678</rekeningnummer>
</klant>
</reservering>'

INSERT INTO  [PRODUCT](
[naam])

SELECT
   Opties = Opties.value('(optie)[1]', 'varchar(50)')
FROM
@XML.nodes('/reservering/opties') AS OptiesTable(Opties)

DECLARE @ProductID AS int
SET @ProductID = @@IDENTITY

INSERT INTO  [WONING](
[naam])

SELECT
   Accomodatie = Reservering.value('(accomodatie)[1]', 'varchar(50)')
FROM
   @XML.nodes('/reservering') AS ReserveringTable(Reservering),
   @XML.nodes('/reservering/opties') AS OptiesTable(Opties),
   @XML.nodes('/reservering/klant') AS KlantTable(Klant)

DECLARE @WoningID AS int
SET @WoningID = @@IDENTITY

INSERT INTO  [KLANT](
[aanhef], [adres], [emailadres], [naam], [postcode], [rekeningnummer])

SELECT
   Aanhef = Klant.value('(aanhef)[1]', 'varchar(50)'),
   Adres = Klant.value('(adres)[1]', 'varchar(50)'),
   Email = Klant.value('(email)[1]', 'varchar(50)'),
   KlantNaam = Klant.value('(naam)[1]', 'varchar(50)'),
   Postcode = Klant.value('(postcode)[1]', 'varchar(50)'),
   Rekeningnummer = Klant.value('(rekeningnummer)[1]', 'varchar(50)')
FROM
  @XML.nodes('/reservering') AS ReserveringTable(Reservering),
  @XML.nodes('/reservering/opties') AS OptiesTable(Opties),
  @XML.nodes('/reservering/klant') AS KlantTable(Klant)

 DECLARE @KlantID AS int
 SET @KlantID = @@IDENTITY

 INSERT INTO  [RESERVERING](
   [aantal_bezoekers], [product], [reserveringsnummer], [status], [van_datum],      
   [tot_datum], [voorkeur], [woning_id], [klant])

 SELECT
    AantalBezoekers = Reservering.value('(aantalBezoekers)[1]', 'int'),
    @ProductID,
    ReserveringNummer = Reservering.value('@reserveringsnummer', 'int'),
    ReserveringStatus = Reservering.value('@status', 'varchar(50)'),
    StartDatum = Reservering.value('(startdatum)[1]', 'date'),
    EindDatum = Reservering.value('(einddatum)[1]', 'date'),
    Voorkeur = Reservering.value('(voorkeur)[1]', 'varchar(50)'),
    @WoningID,
    @KlantID
 FROM
   @XML.nodes('/reservering') AS ReserveringTable(Reservering),
   @XML.nodes('/reservering/opties') AS OptiesTable(Opties),
   @XML.nodes('/reservering/klant') AS KlantTable(Klant)

Collectives™ on Stack Overflow

Inserting XML data in SQL 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