Introduktion till OpenXML i SQL Server

Author: Kristofer Gafvert
First Published: April 3, 2004
Last Updated: April 3, 2004
Last Reviewed: April 3, 2004
PDF: OpenXML-SE.pdf

Innehållsförteckning

Innehållsförteckning
Innan vi börjar
Läsa ett XML-dokument
Ett mer avancerat exempel
Lägga in i en tabell

Innan vi börjar

Lika viktigt som att få XML utifrån data i SQL Server, är det att lyckas lägga in data i SQL Server från ett XML-dokument. Detta använder vi en funktion som heter OpenXML för, men för att det ska fungera, så finns det ett par saker vi måste göra. Innan vi använder OpenXML måste nämligen XML-dokumentet analyseras och byggas upp som en trädstruktur, med de olika noderna, attributen, texterna med mera i XML-dokumentet, i minnet på datorn. Detta använder man en lagrad procedur till som heter sp_xml_preparedocument. Denna lagrade procedur returnerar ett handtag (eng. handle) som man använder för att komma åt detta dokument, som nu finns i minnet med en egen struktur, som SQL Server (och OpenXML) kan tolka.

Eftersom XML-dokumentet nu finns i minnet, betyder det att vi gärna vill ta bort det när vi är klar med det. Om vi inte gör det riskerar vi att göra slut på minnet i datorn, vilket leder till otrevliga saker. Så därför finns det en lagrad procedur för även detta, nämligen sp_xml_removedocument. Om vi nu skulle glömma att ta bort det, så kommer det ändå inte finnas i minnet för all framtid, utan tills anslutningen (den anslutning som användes för att lägga in det i minnet) avbrutits.

Om ni får för sig att kopiera exempel direkt från denna text, istället för att skriva av, och det inte fungerar, kan det bero på att det används en annan teckentabell i detta dokument än vad SQL Query Analyzer använder, vilket betyder att vissa tecken kommer den inte att godkänna.

Läsa ett XML-dokument

Nu när vi vet lite om bakgrunden, är det dags att börja skriva lite T-SQL. Men vi kommer inte gå direkt på att lägga in data i tabeller, utan istället börjar vi med att göra lite vanliga Select frågor, liknande de vi gör för existerande tabeller i en databas, men nu ska vi istället göra det mot det XML-dokument vi har.

1    Declare @resultDoc int, @xmlDoc varchar(4000)
2
3    Set @xmlDoc = N'
4    <?xml version="1.0" encoding="ISO-8859-1"?>
5    <Anställda>
6       <Person>
7          <Förnamn>Anna</Förnamn>
8          <Efternamn>Andersson</Efternamn>
9          <Ålder>26</Ålder>
10      </Person>
11      <Person>
12         <Förnamn>Sivert</Förnamn>
13         <Efternamn>Persson</Efternamn>
14         <Ålder>35</Ålder>
15      </Person>
16   </Anställda>'
17
18   EXEC sp_xml_preparedocument @resultDoc OUTPUT, @xmlDoc
19
20   SELECT *
21   FROM OpenXML(@resultDoc, N'/Anställda/Person', 2)
22   WITH (
23      Förnamn VARCHAR(15),
24      Efternamn VARCHAR(15),
25      Ålder INTEGER
26   )
27
28   EXEC sp_xml_removedocument @resultDoc

Det först ni nog tänker på, varför är XML-koden här, ska inte den vara i en fil? Jo, den kan mycket väl finnas i en fil, men för att saker och ting ska bli enklare, så utelämnas detta (normalt sett är denna kod förmodligen sparad som en lagrad procedur, som exekveras från en applikation, som skickar med XML-dokumentet som text, och inte hänvisar till en fil).

På första raden deklarerar vi två variabler. Den sista av dessa kommer innehålla XML-dokumentet. Denna är deklarerad som varchar, och som ni ser så är den också väldigt stor. För detta lilla exempel hade den inte behövt vara så stor, men man bör tänka på antalet tecken blir otroligt stort när det blir lite större XML-dokument. Om man då anger en för liten storlek på datatypen, kommer det resultera i underliga fel. Till exempel om jag enbart hade angivit storleken 200 i ovanstående exempel, hade vi fått detta felmeddelande:

XML parsing error: The following tags were not closed: Anställda, Person, Efternamn.

Anledningen till detta är för att när den storlek jag angivit har uppnåtts, kommer den strunta i allt som kommer därefter, vilket resulterar att en massa text i XML-dokumentet fattas (vilket inkluderar sluttaggarna för de där elementen).

På rad 3 sparar vi XML-dokumentet (som är på raderna 4 till 16) i variabeln @xmlDoc.

På rad 18 kör vi den lagrade proceduren xp_xml_preparedocument som läser in XML-dokumentet i minnet, formaterar det, och sedan returnerar ett handtag till det, som vi sparar i variabeln @resultDoc.

Därefter, på raderna 20 till 26 gör vi själva frågan, som kommer resultera i detta:

	Förnamn	Efternamn	Ålder
1	Anna	Andersson	26
2	Sivert	Persson	35

OpenXML har som vi ser tre parametrar, och syntaxen ser ut såhär:

OPENXML(idoc int [in],rowpattern nvarchar[in],[flags byte[in]]) 
[WITH (SchemaDeclaration | TableName)]

Den första parametern (idoc) är handtaget till det som skapades med sp_xml_preparedocument. Den andra parametern (rowpattern) används för att tala om vad som är intressant. I vårt fall säger vi åt den att starta i /Anställda/Person (den första / kan utelämnas). Som flagga satte vi nummer 2, detta talade om för den att vi var intresserade av element. Om vi hade satt en 1:a istället, hade vi istället fått attribut (vi saknar attribut helt i vårt exempel, så det hade resulterat i en massa NULL). Man kan också kombinera dessa två nummer till en 3:a, vilket gör att vi får både attribut och element. Därefter använder vi WITH för att tala om vilka kolumner vi vill ha, samt vilken datatyp det är. I vårt fall vill vi ha Förnamn, Efternamn och Ålder från XML-filen, så därför skriver vi det.

På rad 28 rensar vi upp efter oss, och tar bort det som vi lade i minnet.

Ett mer avancerat exempel

Nu när vi har använt SQL för att få ut det vi vill från ett XML-dokument, är det dags att ta ett litet mer avancerat exempel, innan vi slutligen lägger in resultatet i en tabel i vår databas.

1    Declare @resultDoc int, @xmlDoc varchar(4000)
2
3    Set @xmlDoc = N'
4    <?xml version="1.0" encoding=”ISO-8859-1”?>
5    <Anställda>
6       <Avdelning Id="3">
7          <Person Id="1">
8             <Förnamn>Anna</Förnamn>
9             <Efternamn>Andersson</Efternamn>
10            <Ålder>26</Ålder>
11         </Person>
12         <Person Id="2">
13            <Förnamn>Bertil</Förnamn>
14            <Efternamn>Johansson</Efternamn>
15            <Ålder>48</Ålder>
16         </Person>
17      </Avdelning>
18      <Avdelning Id="5">
19         <Person Id="3">
20            <Förnamn>Sivert</Förnamn>
21            <Efternamn>Persson</Efternamn>
22            <Ålder>35</Ålder>
23         </Person>
24      </Avdelning>
25   </Anställda>'
26
27   EXEC sp_xml_preparedocument @resultDoc OUTPUT, @xmlDoc
28
29   SELECT *
30   FROM OpenXML(@resultDoc, N'Anställda/Avdelning/Person', 3)
31   WITH (
32      PersonId INTEGER '@Id',
33      Förnamn VARCHAR(15),
34      Efternamn VARCHAR(15),
35      Ålder INTEGER,
36      AvdelningsId INTEGER '../@Id'
37   )
38
39   EXEC sp_xml_removedocument @resultDoc

Utskrift

	PersonId	Förnamn	Efternamn	Ålder	AvdelningsId
1	1	Anna	Andersson	26	3
2	2	Bertil	Johansson	48	3
3	3	Sivert	Persson	35	5

Som vi ser så är XML-koden lite ändrad. Vi har ni infört några attribut, och vi har också ett helt nytt element – Avdelning. Man skulle kunna likna det här vid två tabeller i en vanlig databas. En tabell som heter Avdelning, och en som heter Person. I Person finns det sedan en Främmande Nyckel från Avdelning, som talar om på vilken avdelning som personen arbetar på. I XML ser detta ut som det som är skrivet här ovan.

På rad 30 och 31, är det lite ändrat. Vi har såklart ändrat ”sökvägen”, då vi fortfarande vill utgå från Person. Vi har också skrivit en 3:a istället för en 2:a, detta för att vi är intresserade av både element och attribut.

Det är på rad 33 till 37 som det intressanta kommer. Vi börjar med rad 33. Precis som tidigare har vi angivit datatypen integer, men nu har vi skrivit ett annat namn för kolumnen. Eftersom vi gjorde detta, och detta namn inte finns i XML-dokumentet, behöver vi en tredje del som talar om vad vi vill ha i XML-dokumentet, nämligen attributet Id. @-tecknet talar om att det är ett attribut vi vill ha, hade vi utelämnat det hade den letat efter ett element som heter Id, vilket vi inte har, och hade resulterar i att det enda vi får är NULL istället för de Id-nummer vi fick. Tilläggas ska också att detta är Case-sensitive. Vi har ett attribut som heter Id, men vi har inget som heter ID, och kan därför inte skriva:

PersonId INTEGER '@ID'

Detta har att göra med att XML är känslig för stora och små bokstäver, och </anställda> kan inte avsluta start-taggen <Anställda>.

På rad 37 kommer något nytt igen. Här anger vi att den ska gå upp en nivå i hierarkin, och hämta attributet Id. Så uppenbarligen är vi inte knytna till en nivå i vårt XML-dokument, utan kan befinna oss var som helst i det och hämta data.

Lägga in i en tabell

Äntligen kommer vi till det mest intressanta, att lägga in det i en tabell. Vi kommer använda oss av föregående exempel, och skapar därför en tabell som heter Person. Därefter lägger vi in data från XML-filen, till denna tabell. Detta blir SQL-frågorna:

1    CREATE TABLE Person
2    (
3       PersonId INTEGER IDENTITY (1,1) NOT NULL,
4       Fornamn VARCHAR(15),
5       Efternamn VARCHAR(15),
6       Alder INTEGER,
7       AvdelningsID INTEGER
8    )
9    GO
10   Declare @resultDoc int, @xmlDoc varchar(4000)
11
12   Set @xmlDoc = N'
13   <?xml version="1.0" encoding="ISO-8859-1"?>
14   <Anställda>
15      <Avdelning Id="3">
16         <Person Id="1">
17            <Förnamn>Anna</Förnamn>
18            <Efternamn>Andersson</Efternamn>
19            <Ålder>26</Ålder>
20         </Person>
21         <Person Id="2">
22            <Förnamn>Bertil</Förnamn>
23            <Efternamn>Johansson</Efternamn>
24            <Ålder>48</Ålder>
25         </Person>
26      </Avdelning>
27      <Avdelning Id="5">
28         <Person Id="3">
29            <Förnamn>Sivert</Förnamn>
30            <Efternamn>Persson</Efternamn>
31            <Ålder>35</Ålder>
32         </Person>
33      </Avdelning>
34   </Anställda>'
35
36   EXEC sp_xml_preparedocument @resultDoc OUTPUT, @xmlDoc
37
38   SET IDENTITY_INSERT Person ON
39   INSERT INTO Person (PersonId, Efternamn, Fornamn, Alder, AvdelningsID)
40   SELECT PersonId, Efternamn, Förnamn, Ålder, AvdelningsID
41   FROM OpenXML(@resultDoc, N'Anställda/Avdelning/Person', 3)
42   WITH (
43      PersonId INTEGER '@Id',
44      Förnamn VARCHAR(15),
45      Efternamn VARCHAR(15),
46      Ålder INTEGER,
47      AvdelningsID INTEGER '../@Id'
48   )
49   SET IDENTITY_INSERT Person OFF
50
51   EXEC sp_xml_removedocument @resultDoc

På rad 1 till 8 skapar vi en ny tabell som heter Person. Därefter på rad 10 till 36 är det exakt likadant som tidigare. Det är inte förren på rad 38 som det blir något nytt. Eftersom vi har en Primär Nyckel som ökas med ett (1) automatiskt, så kan vi inte sätta in vilket värde som helst i den kolumnen när vi gör en INSERT. Utan för att vi ska få sätta in de värden vi har fått från XML-dokumentet, måste vi sätta IDENTITY_INSERT till ON, vilket vi gör här.

På rad 39 och 40 (en rad) börjar vår INSERT-fråga. Den är egentligen inte så konstig, om vi tänker på en vanlig INSERT-fråga med en SELECT-del. För det är precis det vi har här, en INSERT-fråga, som hämtar värdena från en annan tabell genom att använda SELECT.

På rad 41 och 42 väljer vi ut vad vi vill lägga in i tabellen Person. Precis som vid en vanlig INSERT så spelare det ingen roll vad kolumnerna heter, utan det är ordningen på dem.

Rad 43 till 51 är lika som förut, och på rad 52 gör vi motsatsen till vad vi gjorde på rad 38.

Om vi nu gör denna enkla fråga:

SELECT * FROM Person

Får vi detta resultat:

	PersonId	Förnamn	Efternamn	Ålder	AvdelningsId
1	1	Anna	Andersson	26	3
2	2	Bertil	Johansson	48	3
3	3	Sivert	Persson	35	5

Vilket är exakt samma tabell som när vi gjorde en SELECT-fråga direkt mot XML-filen. Kort sagt, vi har lyckats få in vår data i tabellen Person!