Introduktion till OpenXML i SQL Server

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.


     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
    40:

Som vi ser så är XML-koden inte riktigt densamma som innan. Vi har ni infört några attribut, och vi har också ett helt nytt element – Avdelning. Det går att 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 personen arbetar på. I XML ser detta ut som det ovan skrivet .

På rad 30, ä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 32 till 37 som det intressanta kommer. Vi börjar med rad 32. Precis som tidigare har vi angivit datatypen integer, men nu har vi skrivit ett annat namn (PersonId) 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 detta tecken hade den letat efter ett element som heter Id istället. Eftersom vi inte har detta hade det resulterat i att vi fått 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:


     1: PersonId INTEGER '@ID'
     2:

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 36 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 knutna till en nivå i vårt XML-dokument, utan kan befinna oss var som helst i det och hämta data.

Introduktion till OpenXML i SQL Server, del 2

Ett mer avancerat exempel