[SQL server] xml - insert vraag

[Verwijderd]

Het is mogelijk om gebruik te maken van XML om gegevens in te voeren in SQL server.

Stel ik heb het volgende xml:

code:

<Order>
    <OrderLine>
         <ProductName>PC</ProductName>
    </OrderLine>
</Order>

Bij het invoeren van een nieuwe order weet je nog niet wat de orderId is (wat een attribuut kan zijn van de element Order. Ik wil het bovenstaande order invoeren met het volgende Statement

code:

INSERT INTO Order
    SELECT * 
    FROM OPENXML(@hDoc, 'Order/OrderLine') 
          WITH Order
 EXEC sp_xml_removedocument @hDoc
GO

vervolgens wil ik de orderline invoeren door gebruik te maken van dezelfde xml (omdat de order al ingevoerd is kan je de Order id achterhalen met het volgende

code:

declare @orderid int
set @orderId=scope_identity()

nou vraag ik mij af hoe ik dit kan bereiken. Normaal gesproken zal ik op de volgende manier een order invoeren wanneer een orderId aanwezig is:

code:

INSERT INTO OrderLine
    SELECT * 
    FROM OPENXML(@hDoc, 'Order/OrderLine') 
          WITH (
              OrderId int '@OrderId',
              ProductName varchar(10) 'ProductName'
          )
 EXEC sp_xml_removedocument @hDoc
GO

Ik heb echt geen enkele idee hoe ik dit aankan pakken.

Heeft er iemand hier misschien ervaring met soortgelijke probleem?

Greetz B

[MBV]

Ehm, basic SQL lijkt me?

SQL:

INSERT INTO OrderLine (ProductName)
    SELECT *
    FROM OPENXML(@hDoc, 'Order/OrderLine') 
          WITH (
              ProductName varchar(10) 'ProductName'
          )
 EXEC sp_xml_removedocument @hDoc
GO

Daarbij ga ik er vanuit dat dat XML-verhaaltje werkt, want dat ken ik niet

[Verwijderd]

MBV schreef op maandag 28 augustus 2006 @ 21:04:
Ehm, basic SQL lijkt me?

SQL:

INSERT INTO OrderLine (ProductName)
    SELECT *
    FROM OPENXML(@hDoc, 'Order/OrderLine') 
          WITH (
              ProductName varchar(10) 'ProductName'
          )
 EXEC sp_xml_removedocument @hDoc
GO

Daarbij ga ik er vanuit dat dat XML-verhaaltje werkt, want dat ken ik niet

Het probleem is dat ik orderId wil invoeren als foreign key voor de tabel OrderLine. Deze is pas bekend wanneer er een order is aangemaakt.

[MBV]

Verwijderd schreef op maandag 28 augustus 2006 @ 21:40:
[...]


Het probleem is dat ik orderId wil invoeren als foreign key voor de tabel OrderLine. Deze is pas bekend wanneer er een order is aangemaakt.

Oh zo, kennelijk niet goed gelezen. Heb je bij MSSQL niet zoiets als LAST_INSERT_ID() ? Dat zou namelijk de truc zijn bij MySQL voor zover ik weet

[Verwijderd]

MBV schreef op maandag 28 augustus 2006 @ 22:34:
[...]

Oh zo, kennelijk niet goed gelezen. Heb je bij MSSQL niet zoiets als LAST_INSERT_ID() ? Dat zou namelijk de truc zijn bij MySQL voor zover ik weet

ja met scope_identity(). Dit wil ik dus ook gaan gebruiken om de orderLine in te voeren. maar ik heb dus geen idee hoe dit moet met openXML.

[whoami]

Ik snap eigenlijk niet waarom je dit via XML wil doen (openXml dan).

Ik bedoel: je kan toch evengoed die gegevens uit de XML halen, en dan mbhv normale SQL statements de meuk inserten ?

[Feyd-Rautha]

Het gebruik van OpenXML is toch efficienter? Je hoeft nu maar eenmaal een string van tekst naar de server te sturen en klaar is kees. Terwijl met je met verschillende sql-statements werkt er meerdere roundtrips naar de server nodig zijn.

[ Voor 23% gewijzigd door Feyd-Rautha op 29-08-2006 09:13 ]

[whoami]

Ik denk dat dat te verwaarlozen is.
Met die OpenXml toestand wordt je boeltje imho minder leesbaar en moeilijker onderhoudbaar

[Verwijderd]

whoami schreef op dinsdag 29 augustus 2006 @ 10:31:
Ik denk dat dat te verwaarlozen is.
Met die OpenXml toestand wordt je boeltje imho minder leesbaar en moeilijker onderhoudbaar

Dit is denk ik een kwestie van smaak. Het is inderdaad een idee om de XML uit elkaar te trekken en de verschillende elementen en attributen op te slaan in een variablen. Maar eerlijk gezegd hoop ik op een mooiere oplossing.

[MBV]

en wat gebeurt er als je scope_identity() gebruikt in de 2e query?

[pistole]

kunnen er meerdere orders voorkomen in je XML documentje? In dat geval zal je met een cursor hierdoorheen moeten gaan lopen. Dan kan je de records per stuk inserten en kan je na de insert de toegewezen id (@@IDENTITY) weer opvragen.

[Verwijderd]

pistole schreef op dinsdag 29 augustus 2006 @ 16:18:
kunnen er meerdere orders voorkomen in je XML documentje? In dat geval zal je met een cursor hierdoorheen moeten gaan lopen. Dan kan je de records per stuk inserten en kan je na de insert de toegewezen id (@@IDENTITY) weer opvragen.

op dit moment kunnen er maar slecht 1 order voorkomen. Het probleem is dus met die foreign keys dat dus niet aanwezig (hoeft) te zijn bij het invoeren van een nieuwe order. Deze moet je dus met scope_identity() op gaan halen. Maar de vraag is hoe ik zoiets kan combineren met openxml.

[pistole]

ik ken scope_identity() helaas niet. Dmv OpenXML kan je gelukkig wel je bron als "tabel" benaderen, dus alle manieren die je normaal op een tabel toepast werken hier ook

[P_de_B]

Ik ben het eigenlijk wel eens met whoami, waarom dit openXML gebruiken? In mijn ogen is dat niet meer dan een halfbakken poging om met de (toenmalige) XML hype mee te geen, echt praktische toepassingen heb ik nog niet kunnen zien. Ik denk dat je beter via normale SQL statements de data kunt toevoegen.

Ik heb zelfs wel eens een applicatie gezien waarin aan de clientkant XML werd gegenereerd om dat naar de SQL Server te sturen

[P_de_B]

pistole schreef op dinsdag 29 augustus 2006 @ 20:28:
ik ken scope_identity() helaas niet.

Scope identity is vergelijkbaar met @@IDENTITY met als verschil dat het echt de laatst gegenereerde IDENTITY in de batch retourneert. Met @@IDENTITY kun je een identity terugkrijgen die door een trigger is gemaakt, je kunt dan dus de verkeerder identity terugkrijgen.

Je doet bijvoorbeeld een INSERT in een klanten tabel, en direct daarna SELECT @@IDENTITY om de nieuwe ID terug te krijgen, als er op de klantentabel een trigger zit die in een andere tabel wat gegevens toevoegd, krijg je de identity waarde van de andere tabel en niet die uit de klanten tabel. scope_identity zal wel de waarde uit de klantentabel geven.

[Annie]

Is dit wat je bedoeld?

SQL:

INSERT INTO OrderLine (OrderId, ProductName)
    SELECT @orderId, * 
    FROM OPENXML(@hDoc, 'Order/OrderLine', 2) 
          WITH (ProductName VARCHAR(10))

[Annie]

P_de_B schreef op dinsdag 29 augustus 2006 @ 20:48:
[...]

Scope identity is vergelijkbaar met @@IDENTITY met als verschil dat het echt de laatst gegenereerde IDENTITY in de batch retourneert.

Om het verhaal compleet te maken een quote uit de books.

[quote]IDENT_CURRENT is similar to the Microsoft® SQL Server™ 2000 identity functions SCOPE_IDENTITY and @@IDENTITY. All three functions return last-generated identity values. However, the scope and session on which 'last' is defined in each of these functions differ.

• IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
• @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
• SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

[whoami]

P_de_B schreef op dinsdag 29 augustus 2006 @ 20:45:
Ik ben het eigenlijk wel eens met whoami, waarom dit openXML gebruiken? In mijn ogen is dat niet meer dan een halfbakken poging om met de (toenmalige) XML hype mee te geen, echt praktische toepassingen heb ik nog niet kunnen zien. Ik denk dat je beter via normale SQL statements de data kunt toevoegen.

Nou, ik heb er één praktische toepassing van gezien (en gebruik ze ook).
We leveren XML bestanden aan die vanuit een bepaalde DB komen. Die data moet dan in een test db'tje komen (die een andere, simpele structuur heeft), zodanig dat we er een aantal tests kunnen op doen, om te verifieren of de data wel correct is.
Daar hebben we dus een programmatje voor geschreven, dat een script genereert die de nodige SQL Statements genereerd die mbhv OPENXML die gegevens direct in die DB gooien. Daar is het wel nuttig voor. Voor de rest heb ik dat openxml nog nooit tegengekomen, en ik zou er ook niet direct een zinvolle toepassing kunnen voor bedenken.

Ook niet in het geval van de TS. Het lijkt me alsof hij bezig is met een OLTP DB, waarbij hij de transacties eerst in een XML giet om ze daarna in de DB te proppen. Hoezo , onnodige tussenstap die veel complexiteit toevoegt maar geen toegevoegde waarde biedt ? _{maar ik kan verkeerd zijn natuurlijk}

[Verwijderd]

whoami schreef op dinsdag 29 augustus 2006 @ 22:36:
[...]
Nou, ik heb er één praktische toepassing van gezien (en gebruik ze ook).
We leveren XML bestanden aan die vanuit een bepaalde DB komen. Die data moet dan in een test db'tje komen (die een andere, simpele structuur heeft), zodanig dat we er een aantal tests kunnen op doen, om te verifieren of de data wel correct is.
Daar hebben we dus een programmatje voor geschreven, dat een script genereert die de nodige SQL Statements genereerd die mbhv OPENXML die gegevens direct in die DB gooien. Daar is het wel nuttig voor. Voor de rest heb ik dat openxml nog nooit tegengekomen, en ik zou er ook niet direct een zinvolle toepassing kunnen voor bedenken.

Ook niet in het geval van de TS. Het lijkt me alsof hij bezig is met een OLTP DB, waarbij hij de transacties eerst in een XML giet om ze daarna in de DB te proppen. Hoezo , onnodige tussenstap die veel complexiteit toevoegt maar geen toegevoegde waarde biedt ? _{maar ik kan verkeerd zijn natuurlijk}

OpenXML wordt gebruikt omdat xml als formaat wordt gebruikt voor het verzenden van gegevens tussen de verschillende applicaties. Dit is ook de reden waarom er openxml gebruikt wordt voor het "gemakkelijk" opslaan van gegevens.