SQL Server 2005: de opmars van het XML datatype

In SQL Server 2000 is een eerste aanzet gedaan om een integratie tot stand te brengen tussen SQL Server en XML. We hebben toen voor het eerst kennis gemaakt met de ‘FOR XML‘ clausule in een SELECT Statement om relationele data in XML formaat uit de database te krijgen. We konden queries uitvoeren via Http, data muteren via UpdateGrams, data zoeken met XPath queries en OpenXML gebruiken om XML data op een relationele manier te bekijken. In SQL 2005 doet Microsoft er een schepje boven op door bestaande XML functionaliteit te verbeteren en nieuwe XML functionaliteit toe te voegen. Een van deze nieuwe functionaliteiten is de introductie van het nieuwe XML datatype. In dit artikel ga ik dieper in op dit nieuwe XML datatype.

XML datatype

Het grote nieuws betreffende de XML integratie in SQL Server 2005 is zeker de introductie van het XML datatype. Konden we in SQL 2000, XML opslaan als een varchar, text of in een blob, SQL 2005 heeft zijn eigen XML datatype. Intern wordt het XML datatype opgeslagen als een binaire representatie welke geoptimaliseerd is voor parsing. Elke veld van het XML datatype kan maximaal 2 gigabyte groot zijn, waarbij de maximale hiërarchie-diepte 128 niveaus is. Het XML datatype kan gebruikt worden voor XML kolommen, parameters en variabele declaraties. Doordat XML nu wordt opgeslagen als XML en niet als een text string of als een blob, heeft dat als gevolg dat het XML datatype zijn eigen methodes heeft om met behulp van XQuery de XML data te valideren of te modificeren.

Het grote nieuws betreffende de XML integratie in SQL Server 2005 is zeker de introductie van het XML datatype

Het XML datatype is er in twee smaken; untyped en typed. Als een XML een schema heeft, spreken we van een typed XML. Heeft een XML echter geen bijbehorend schema, dan spreken we van een untyped XML. Elke smaak heeft zijn eigen voor- en nadelen. Een untyped XML datatype kan allerlei XML structuren bevatten en is bijvoorbeeld niet gebonden aan een schema.

CREATE TABLE MyNewXMLTable
(
  Id IDENTITY(1,1) NOT NULL PRIMARY KEY
  MyXmlColumn XML not null
)

Listing 1: Creatie van een tabel met een untyped XML kolom

Een typed XML heeft alleen maar XML data die voldoet aan een XML schema. Het XML schema  valideert de data, verzorgt betere type checking en zorgt voor optimalisatie bij de opslag van de XML en bij het queryen op de XML. 

CREATE TABLE MyNewXMLTable
(
  Id IDENTITY(1,1) NOT NULL PRIMARY KEY
  MyXmlColumn XML (CONTENT myXMLSchema)
)

Listing 2: Creatie van een tabel met een typed XML kolom

Net als voor alle andere datatypes kun je ook op het XML datatype constraints leggen, maar je kunt niet alle types constraints toepassen. Zo kunnen om begrijpelijke redenen de volgende constraints niet op een op een XML datatype toegepast worden:

• Unique constraints,
• Primary key constraints,
• Foreign key constraints.

Daarentegen kunnen we wel check constraints of default values definiëren op een XML datatype.

CREATE myCheckConstraintUDF( @XmlData XML)
RETURNS int AS
BEGIN
  -- logica om het XML document te valideren
  -- op bijvoorbeeld een ID of een
  -- naam
END
GO

CREATE TABLE MyNewXMLTable
(
  Id IDENTITY(1,1) NOT NULL PRIMARY KEY
  MyXmlColumn XML not null
  CONSTRAINT myCheckConstraint CHECK
    (myCheckConstraintUDF(MyXmlColumn)  = 0)
)

Listing 3: Creatie van een tabel met een check constraint op een XML kolom

XML indexen

Zoals eerder aangegeven wordt de data in het XML datatype opgeslagen in een intern binair formaat. Aangezien het XML datatype 2 gigabyte aan data kan bevatten, kan het doorzoeken van een XML datatype lang duren. Op een XML dataype kunnen verschillende soorten indexen gelegd worden; primaire en secundaire indexen. De secundaire indexen zijn weer onder te verdelen in drie verschillen sub-soorten.  Een index versnelt het zoeken naar data; queries worden gecompileerd tegen de primaire index van het XML datatype. Er wordt een query plan gecreëerd voor de gehele query. De secundaire indexen worden gebruikt op basis van de uitkomsten van de query optimizer.

Op een XML dataype kunnen verschillende soorten indexen gelegd worden; primaire en secundaire indexen

Willen we gebruik maken van de secundaire indexen, dan moet er eerst een primaire index worden gecreëerd. Er worden drie type secundaire indexen onderkend

• PATH index; de PATH index creëert een index op de kolommen path, value van de primaire index. Deze index wordt o.a gebruik om het zoeken met de exist() methode te versnellen.
• PROPERTY index; de PROPERTY index creëert een index  op de primary key, path, value kolommen van de primaire index. Deze index versnelt het zoeken naar values.
• VALUE index creëert een index op de kolommen value, path van de primaire index. Deze index versnelt het zoeken als een bepaalde waarde bekend is, maar het exacte pad niet. Bijvoorbeeld //auteur/achternaam[.=’Wolkers’]. Ook voor wildcard zoekopdrachten is deze index geschikt.

CREATE XML MyNewXMLTable_Index
  on MyNewXMLTable(MyXmlColumn)
USING XML INDEX MyNewXMLTable_Index FOR PATH

Listing 4: Creatie van een PATH index

Methoden op het XML datatype

Het XML datatype heeft zijn eigen methoden:

• exist(),
• nodes(),
• query(),
• value(),
• modify().

De eerste vier methodes gebruiken XQuery, de laatste gebruikt een uitbreiding op XQuery om data te updaten.

Om te controleren of bepaalde data aanwezig is in het XML datatype, maakt het XML datatype gebruik van de methode exist(). Deze methode wordt gebruikt in het where statement van de T-SQL query. De methode exist() retourneert 0 als de Xquery expressie geen resultaat oplevert. Als de expressie wel een waarde oplevert, retourneert  de methode 1.

SELECT * FROM MyNewXMLTable WHERE
MyXmlColumn.exist(‘/book[@isbn=1234567890]’) = 1

Listing 5: Gebruik van de exist() methode

De nodes() methode maakt het mogelijk om XML data relationeel weer te geven. De nodes() methode definieert een tabel en een kolom. De syntax is: [Xml datatype].nodes( Xquery) as Table(Column).  De geselecteerde node in de Xquery wordt in de kolom van de betreffende tabel geplaatst.  Het resultaat van de nodes() methode is een tabel met één kolom. De regels in deze kolom bevatten een kopie van de originele XML instantie, alleen de context node is gezet naar de node die door de XQuery is aangegeven. Het datatype van deze kolom is dus een XML node, waar we weer de methoden exist, query en value op toe kunnen passen.

SELECT MyNodeTable.NewNodeColumn.query(‘.’)
  as NodeResult
  FROM
  @MyXmlVariable.nodes(‘/book/author’)
  MyNodeTable(NewNodeColumn)

Listing 6: Gebruik van de nodes() methode

Met behulp van de query() methode is het mogelijk data te zoeken en te retourneren in een XML document. De Xquery expressie evalueert naar een list met untyped XML nodes.

SELECT * FROM MyNewXMLTable WHERE
MyXmlColumn.query(‘/book[@isbn=1234567890]’)

Listing 7: Gebruik van de query() methode

In tegenstelling tot de query() methode retourneert de value() methode geen nodelist maar een enkele waarde. De value() methode vraagt twee parameters: de XQuery expressie en het T-SQL datatype waarin het resultaat geplaatst moet worden. Niet alle T-SQL datatypes worden ondersteund; de datatypes xml, image, text, ntext, timestamp en user defined datatypes worden niet ondersteund.

SELECT MyXmlColumn.value(
  ‘/book[@isbn=1234567890]/title’,NVarchar(100))
  FROM MyNewXMLTable

Listing 8: Gebruik van de value() methode

De hierboven genoemde XML methoden kunnen niet gebruikt worden in een GROUP BY clause van een SQL statement. Wel is het mogelijk om het resultaat van een XML methode in een GROUP op te nemen, b.v. het resultaat van de value() methode.

SELECT COUNT(Books), MyXmlColumn.value(
  ‘/book[@isbn=1234567890]/author’,
  NVarchar(100)) as author FROM MyNewXMLTable
GROUP BY author

Listing 9: Gebruik van de value() methode en een GROUP BY clause

De laatste functie die we hier bespreken is de modify() methode. Om XML data te modificeren heeft Microsoft een uitbreiding op de Xquery standaard gedaan en deze XML Data Modification Language (XML DML) genoemd. XML DML introduceert nieuwe keywords als insert, delete en replace value of om data modificatie te ondersteunen in het XML datatype.

In de books online van SQL Server 2005 worden voorbeelden gegeven voor het toevoegen, verwijderen en wijzigen van elementen, attributen, commentaar, tekst, etc. Hier beperken we ons tot de syntax van de bijbehorende keywords en enkele eenvoudige voorbeelden:

insert
Expression1 (
  {as first | as last} into | after | before }
Expression2 )

Listing 10: Syntax insert statement voor de modify() methode

Met behulp van de keywords as first, as last, after en before wordt de locatie aangegeven waar de nodes ingevoegd moeten worden.

DECLARE @xmlDoc xml
SET @xmlDoc = ‘
      Booktitle=”MyFirst book title”>
    
    
 
’

SELECT @xmlDoc
SET @xmlDoc.modify(‘insert
  My first bookreview
as last
into (/Root/BookDescription/Bookreviews)[1]’)

Listing 11: Gebruik van het insert keyword in de modify()  methode

Om één of meerdere nodes uit een XML-structuur te verwijderen wordt het keyword delete gebruikt. Het delete- statement wordt gevolgd door een XQuery expressie die de te verwijderen nodes aangeeft.

delete Expression

Listing 12: Syntax delete statement voor de modify() methode

SET @xmlDoc.modify(‘delete
 /Root/BookDescription/Bookreviews/Bookreview[@ID=”1”]’)

Listing 13: Gebruik van het delete keyword in de modify()  methode

Om een node in een XML-strcutuur te wijzigen wordt het keyword replace value of gebruikt.

Het replace statement wordt gevolgd door twee XQuery expressies: één die de te wijzigen node aangeeft en één die gewijzigde node aangeeft.

Replace value of
  Expression1 
    with
  Expression2

Listing 14: Syntax replace statement voor de modify() methode

Expression1 moet een attribuut, een text of een simple type element zijn. Als Expression1 van een ander type is, wordt een fout gedetecteerd. Afhankelijk of het XML datatype typed of untyped is, worden er voorwaarden gesteld aan Expession2. Als het XML datatype untyped is, dan moet Expression2 een expressie zijn die een atomaire waarde oplevert. Als het XML datatype typed is, dan moet Expression2 van het zelfde type zijn van Expression1, of een sub type zijn van expression1.

SELECT @xmlDoc
SET @xmlDoc.modify(
 ‘replace value of (/Root/BookDescription/@Booktitle)[1]
    with ‘My new booktitle’)

Listing 15: Gebruik van het replace value of keyword in de modify()  methode

Conclusie

In dit artikel hebben we de basis van het XML datatype bekeken. We hebben gezien hoe we een XML datatype kunnen definiëren en hoe we met behulp van de methoden op dit datatype XML data kunnen ontsluiten. Natuurlijk is niet alles rozengeur en maneschijn. Het XML datatype en het gebruik ervan heeft ook zijn beperkingen, zo kan het XML datatype bijvoorbeeld niet gebruikt worden in een GROUP BY clausule en kan het niet gebruikt worden in een heleboel functies van SQL Server. Echter, deze beperkingen wegen niet op tegen de voordelen die het XML datatype met zich mee brengt. Het XML datatype biedt ons programmeermogelijkheden die voorheen ondenkbaar of moeilijk realiseerbaar waren. Met behulp van het XML datatype kunnen we nu XML data opslaan en ontsluiten op een meer intuïtieve manier dan in het verleden. Als we daar de nieuwe index technieken bij optellen, die we op het XML datatype kunnen zetten, dan kunnen we concluderen dat het ontwikkelen met het XML datatype in allerlei opzichten een behoorlijke snelheidswinst gaat opleveren.

Referenties

SQL Server 2005 Books online