Introduktion till OpenXML i SQL Server

Author: Kristofer Gäfvert
First Published: April 3, 2004
Last Updated: November 18, 2004
Last Reviewed: November 18, 2004
PDF: OpenXML-SE.pdf
Download Code: OpenXML-SE-Code.Zip

Innehållsförteckning

Innan vi börjar

Lika viktigt som att få XML utifrån data i SQL Server, är det att utifrån ett XML-dokument lyckas lägga in data i SQL Server. 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 tänka på. Innan vi använder OpenXML måste 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. För detta används en lagrad procedur som heter sp_xml_preparedocument. Denna lagrade procedur returnerar ett handtag (eng. handle) som används 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 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 en minnesläcka. Så även för detta finns det en lagrad procedur, som heter sp_xml_removedocument. Om vi nu skulle glömma att ta bort XML-dokumentet från minnet, så kommer det ändå inte finnas där 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 er att kopiera exempel direkt från denna text, istället för att skriva av, och det inte fungerar, så kan det bero på att jag använder en annan teckentabell i detta dokument än vad SQL Query Analyzer använder. Detta kommer att leda till att SQL Query Analyzer inte kommer att godkänna användningen av dessa tecken.

Läsa ett XML-dokument

Nu när vi vet lite om bakgrunden, så är det dags att börja skriva lite T-SQL. Men vi kommer inte att 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. Dessa frågor liknar 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
    29:

Det första ni nog funderar över är varför XML-koden är 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 att 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 tänkas bör på att 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 detta 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 felmeddelandet ä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 elementen ”Anställda”, ”Person” och ”Efternamn”).

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:


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

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, vilket betyder att vi var intresserade av element. Om vi hade satt en 1:a istället, hade vi fått attribut (vi saknar attribut helt i vårt exempel, så det hade resulterat i en massa NULL). Det går 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.