XML Database

This page contains information about storing and quering XML in databases

SQL server

XML datatype

CREATE XMLSCHEMA COLLECTION SCHEMANAME AS '
<--XML Schema -->
'
CREATE TABLE XMLDocs (
DocID INTERGER IDENTITY PRIMARY KEY,
XMLDoc XML (SCHEMANAME)
)

CREATE FULLTEXT INDEX on XMLDocs(XMLDoc)

INSERT XMLDocs
VALUE ('<root><ELEMENTNAME>TEXT</ELEMENTNAME></root>')

SELECT XMLDoc FROM XMLDocs

<-- allows methods query(), value(), exist (), modify(), nodes() -->
SELECT XMLDoc.query('
  <!-- XQUERY -->
FROM ELEMENTNAME

SELECT XMLDoc.value('
   /XPATH/NODE', 'DATATYPE')
FROM ELEMENTNAME Where ELEMENTNAME = TEXT

SELECT XMLDoc.query('XPATH/NODE')
FROM ELEMENTNAME WHERE XMLDOIC.exist('XPATH/NODE')

UPDATE ELEMENTNAME
SET XMLDoc.modify ('replace value of
  XPATH/NODE
  with "TEXT"
 ')
Where ELEMENTNAME ='TEXT'

UPDATE ELEMENTNAME
SET XMLDoc.modify
  ('delete XPATH/NODE')
Where ELEMENTNAME ='TEXT'

SELECT xml_schema_namespace ("http://XMML.com/ExampleNamespace")

DROP XMLSCHEMA NAMESPACE "http://XMML.com/ExampleNamespace"

Data Modification Language

Delete

declare @XMLDoc XML
SET @XMLDoc = '<root><element>TEXT</element></root>'
SET @XMLDoc.modify('
  delete /root/element
  ')
SELECT @XMLDoc

Insert

declare @XMLDoc XML
SET @XMLDoc = '<root><element>TEXT</element></root>'
SET @XMLDoc::modify('
  insert <element2>TEXT<element2>
  as first
  into /root/element[1]
  ')
SELECT @XMLDoc

Update

declare @XMLDoc XML
SET @XMLDoc = '<root><element>TEXT</element></root>'
SET @XMLDoc.modify('
  replace value /root/element
  to "TEXT2"
  into /root/element[1]
  ')
SELECT @XMLDoc

Query

SELECT XMLDoc.query('
  <-- XQUERY -->
')

---

XQUERY

W3C Recommendation

http://www.w3.org/xml/xquery
functions: http://www.w3.org/tr/xpath-functions

FLWOR expressions

<items>
 {for $i in (1 to 5, 8, 9)
   let $t := $i
   where $t > 1
   order by $t descending
   return 
    if ($t > 2)
     then
      <item>{$t}</item>
   else ()
   }
</items>

---

XQuery functions

 <output>
 {
  for $i in doc('Example.xml')/Parts/Part[1]
  for $j in doc('Example.xml')/Parts/Part[2]
  return
  concat($i, $j)
 }
 </output>

let $i := doc('Example.xml')/Parts/Part[1]
return
 <aggregate> {count|avg|sum|max|min($i)} </aggregate>

for $i in doc('Example.xml')/Parts/Part[1]
where concat|starts-with|ends-with|contains|substring|string-length|normalize|upper-case|lower-case ($i/NODE, "VALUE")
return $i/NODE

---

XQuery user defined functions

declare function PREFIC:FUNCTION_NAME($PARAMETER AS XS:DATATYPE)
  AS XS:RETURNDATATYPE
{
 let $i :=$PARAMETER * 100
 return $i
}

<result>
  {PREFIX:FUNCTION_NAME ($XPATH/NODE)
</result>

---

Examples

(: example.xquery :)
(: process several XML documents collection(xsd:anyURI) :)
(: xquery node - document, element, attribute, namespace, text, comment, processing instruction :)
(: supported axes - child, parent, descendant, descendant-or-self :)
(: full axis only supported - ancestor, ancestor-or-self, following, following-sibling, preceding, preceding-sibling :)

xquery version '1.0';
module namespace XMML = 'http://www.XMML.com/XQueryModule/';
declare base-URI 'http://data.nbn.org.uk/';
declare namespace admin = 'http://data.nbn.org.uk/organisation'
declare namespace xs = 'http://www.w3.org/2001/XMLSchema
declare default element namespace 'http://data.nbn.org.uk/dataset'
declare default functiion namespace 'http://www.functionexample.org'
import schema namespace xs = 'http://www.w3.org/2001/XMLSchema
  at 'http://SchemaLocation.xsd"
declare variable $VARIABLE as xs:string {'VALUE'};
declare variable $PARAMETER as xs:string external;
declare validation lax|skip|strict;
(:handle whitespace:)
declare xmlspace = strip|preserve;

(: get administrator example :)
<administrators number="{count(doc('dataset.xml')/nbnexchange/dataset/admin:administrators/admin:administrator)}  >
  {for $administrator in doc('dataset.xml')/nbnexchange/dataset/admin:administrators/admin:administrator
   where $administrator/@userkey = 'VALUE'
   return
   element administrator {
     attribute userkey {$administrator/@userkey}
     element name {$administrator/admin:name/text()}
     }
    }
</administrators>