XML in SQL Server 2005

XML in SQL Server 2005
Technologie Memo
Januar 2006
Letzte Revision 8. Juni 2007
© Dr. Arno Schmidhauser
XML in SQL Server 2005
Inhalt
1
2
3
4
5
6
7
8
Referenz
XML Typ
Schema
XQuery
4.1 Beispiele ohne Verwendung eines XML-Schema
4.2 Beispiele mit Verwendung eines XML-Schema
Indexierung
Volltext-Fuktionalität
Schema Evolution
Schema Einschränkungen
Technologie Memo
Arno Schmidhauser
3
3
3
4
4
5
6
7
9
9
2
XML in SQL Server 2005
1 Referenz
Die folgenden Kapitel beziehen sich auf Microsoft SQL Server 2005 (Version 9.00).
2 XML Typ
Der Datentyp XML gehört zum SQL-Umfang. Als Basis wird ein Blob-Typ
(Binary Large Object) verwendet. Ein Tabelle, welche XML-Spalten enthält,
kann wie folgt definiert werden:
create table tablename (
…, attributname xml, … )
Ohne weitere Angaben kann dieses Attribut allgemeinen, wohlgeformten
XML-Content aufnehmen. Es ist nicht notwendig, dass nur ein einziges
Root-Element existiert.
3 Schema
Ein XML Schema kann in einer Schema Collection abgelegt werden mit:
create xml schema collection mySchemaColl as
N'<?xml version="1.0" ... ?><xsd:schema> ... </xsd:schema>'
Eine Schema Collection kann nicht gelöscht werden, solange sie in Gebrauch ist. Mit alter xml schema collection können weitere Definitionen in die Collection eingefügt, aber keine daraus entfernt werden. Einmal
eingefügte Schemas können nicht mehr geändert werden.
Eine Tabelle mit XML-Attributen kann sich auf diese Collection beziehen
mit:
create table tablename ( attname xml (mySchemaColl) )
oder
create table tablename ( attname xml (document mySchemaColl) )
oder
create table tablename ( attname xml (content mySchemaColl) )
Technologie Memo
Arno Schmidhauser
3
XML in SQL Server 2005
Das Schlüsselwort document besagt, dass in attname nur ein wohlgeformte XML-Dokument (ein einziges Root-Element) vorkommen darf. Ansonsten wären mehrere XML-Fragmente im gleichen Feld erlaubt, auch gemischt mit Text-Inhalt. Auf welches Schema in der Schema Collection sich
ein XML-Dokument bezieht, steht im XML-Dokument selber via das xlmnsAttribut1 im <schema>-Element.
Die definierten Schemas und Namespaces einer Datenbank können mit
folgenden SQL-Befehlen gefunden werden:
select * from sys.xml_schema_collections
select * from sys.xml_schema_namespaces
4 XQuery
Für die Abfrage von XML-Content wird XQuery verwendet. Auch das selektive Ändern von XML-Zellen in einer Tabelle baut auf XQuery auf: Mit
XPath wird adressiert, wo geändert/eingefügt/gelöscht werden soll, zusätzliche Klauseln beschreiben die einzufügenden oder neuen Daten.
Auch Variablen oder Attribute aus der umgebenden Abfrage (
sql:variable() und sql:column() ) können als Parameter in die Abfrage hineingenommen werden.
4.1 Beispiele ohne Verwendung eines XML-Schema
create table Diplombeurteilung (
idBeurteilung numeric(18,0) primary key,
beurteilung xml not null
);
insert into Diplombeurteilung ( idBeurteilung, beurteilung)
values ( 1, N'...' )
select beurteilung.query( '//titel' )
from Diplombeurteilung
select beurteilung.value( '(//kopf/titel)[1]', 'varchar(30)' )
from Diplombeurteilung
select beurteilung.query( '//student' )
from Diplombeurteilung
where beurteilung.exist( '//note/gesamt/[ectsnote="B"]' ) = 1
1
Das Attribut xsi:schemaLocation wird ignoriert, der Ort des Schemas ist ja die
Schema Collection selbst
Technologie Memo
Arno Schmidhauser
4
XML in SQL Server 2005
update Diplombeurteilung
set beurteilung.modify( 'insert <normwert>0.95</normwert> as first into
(/diplombeurteilung/note/gesamt)[1]')
where idBeurteilung = 1
update Diplombeurteilung
set beurteilung.modify( 'insert <ectsnote>A</ectsnote> after
(/diplombeurteilung/note/gesamt/normwert)[1]')
where idBeurteilung = 1
update Diplombeurteilung
set beurteilung.modify( 'delete /diplombeurteilung/note/gesamt/*')
where idBeurteilung = 1
update Diplombeurteilung
set beurteilung.modify( 'replace value of
(/diplombeurteilung/note/gesamt/ectsnote/text())[1] with "B"')
where idBeurteilung = 1
declare @note varchar(1)
set @note = 'B'
select beurteilung.query( '//ectsnote[.= sql:variable( "@note" )]' )
from Diplombeurteilung
4.2 Beispiele mit Verwendung eines XML-Schema
create xml schema collection Diplombeurteilung as
N'<?xml version="1.0"?><xsd:schema
targetNamespace=http://www.sws.bfh.ch/diplombeurteilung>
...
</xsd:schema>';
create table Diplombeurteilung (
idBeurteilung numeric(18,0) primary key,
beurteilung xml (document Diplombeurteilung ) not null
);
insert into Diplombeurteilung ( idBeurteilung, beurteilung)
values ( 1, N'<?xml version="1.0" ?>
<diplombeurteilung xmlns="http://www.sws.bfh.ch/diplombeurteilung">
...
</diplombeurteilung>' );
select beurteilung.query( '
declare default element namespace
"http://www.sws.bfh.ch/diplombeurteilung";
//titel' )
from Diplombeurteilung;
update Diplombeurteilung
set beurteilung.modify( '
declare default element namespace
"http://www.sws.bfh.ch/diplombeurteilung-sqlserver-2005";
insert <normwert>0.95</normwert> as first into
/diplombeurteilung/note/gesamt
' )
where idBeurteilung = 1
blau:
Standard XQuery
Technologie Memo
Arno Schmidhauser
5
XML in SQL Server 2005
rot:
grün:
Microsoft Features
XML Schema bezogene Angaben
5 Indexierung
Erstellung eines Primärindex mit:
create primary xml index ixname on tablename ( attname )
Der Primärindex enthält einen Eintrag pro Knoten im XML-Dokument. Jeder Indexeintrag enthält folgende Information:
ƒ
ƒ
ƒ
ƒ
ƒ
ƒ
ƒ
ƒ
ƒ
ƒ
ƒ
ƒ
Knoten-ID im Dokument (z.B. 1.1, oder 1.2.3), ORDPATH-Format
Knotenart ( z.B. Element, Attribut, Kommentar ), nur Nr
Name des Elementes ( z.B. Person, Adresse )
Namespace URI
XML Schema Typ (z.B. xs:string oder xs:date ), nur Nr
Wert kurz, sql_variant Typ (max 8000 Bytes)
Wert lang binär (nvarbinary(max))
Wert lang character (nvarchar(max))
Pfad
Dokument-ID, Fremdschlüssel auf Dokument-Tabelle.
typed-Flag
nil-Flag
Die Knotenart und der XML Schema Typ werden zudem als Integer codiert, die eigentlichen Namen in einer zusätzlichen Map aufbewahrt. Der
Primärindex vermeidet die Zerlegung eines Dokumentes in einzelne Knoten zur Laufzeit einer Abfrage. Wenn ein XQuery durchgeführt wird, sucht
dieses für jedes Dokument alle Indexeinträge ab. Eine SQL-Abfrage, welche eine Tabelle nur über eine XQuery-Bedingung einschränkt, muss also
immer noch alle Daten der ganzen Tabelle bearbeiten. Lediglich die Zerlegung eines Dokuments in Knoten wird überflüssig durch den Index.
Der Index benötigt ca. einen Faktor 3 an Platz gegenüber den originalen
Daten.
Mit einem Sekundärindex kann die Suche noch einmal wesentlich beschleunigt werden. Sekundärindexe erfordern einen bestehenden Primärindex. Während der Primärindex eigentlich eine gewöhnliche Tabelle mit
spezieller Semantik ist, sind die Sekundärindexe normale SQL-Indexe auf
dem XML Primärindex. Es kann ein Sekundärindex auf dem Pfad, dem
Wert, oder den Knoteneigenschaften erstellt werden:
create xml index ixname on tablename ( attname )
using xml index primindexname
for path | for value | for property
Technologie Memo
Arno Schmidhauser
6
XML in SQL Server 2005
Beim Pfad-Index ist das Hauptsuchkriterium der Pfad eines Knotens im
Dokument, zusätzlich wird der Wert des Knotens mitgeführt. Es werden
also XQuery-Abfragen mit eindeutigen Pfadangeben verbessert, z.B.
/book/author/name/firstname[.='Harold']
Beim Value-Index ist das Hauptsuchkriterium der Wert eines Knotens im
Dokument, zusätzlich wird der Pfad des Knotens mitgeführt. Es werden
also XQuery-Abfragen mit =, <, > Vergleichsoperatoren verbessert, z.B.
//firstname[.='Harold']
In diesem Beispiel ist der Value-Index günstiger, weil der Pfad nicht eindeutig spezifiziert ist.
Beim Property-Index ist das Hauptsuchkriterium der DokumentPrimärschlüssel, gefolgt vom Pfad und vom Wert eines Knotens im Dokument. Es werden XQuery-Abfragen mit mehreren Bedingungen im selben
Dokument verbessert.
Artikel zur Indexierung:
http://www.microsoft.com/indonesia/msdn/xmlindexes.aspx#xmlindexes_topic2
http://www.cs.umb.edu/~poneil/ordpath.pdf
6 Volltextsuche
XML-Dokumente können grössere oder kleiner Anteile an unstrukturiertem
Text haben. Diese Textblöcke können gemischt mit XML-Elementen (mixed content) oder innerhalb eines XML-Elementes auftreten. Textblöcke
können zusammen mit kleinen, hochstrukturierten Elementen auftreten,
oder im Extremfall das ganze Dokument ausmachen.
<x>
<x>
</x>
</x>
<x>
<x>
</x>
<x>
<x>
</x>
<x>
Technologie Memo
</x>
<x>
</x>
</x>
<x>
Vollstrukturiertes
XML-Dokument
Die einzelnen Knotenwerte werden i.a. atomar behandelt beim
Einfügen, Suchen, Ersetzen, Löschen.
</x>
</x> <x>
</x>
Arno Schmidhauser
7
XML in SQL Server 2005
<x>
<x>
</x>
<x>
Semistrukturiertes
XML-Dokument
</x>
<x>
</x>
</x>
<x>
</x>
<x>
</x>
<x>
</x>
Die Knotenwerte werden i.a. atomar behandelt beim Einfügen,
Ersetzen, Löschen. Abfragebedingungen sind (oder werden
mit Vorteil) über die kleinen
Knotenwerte definiert. Die Textblöcke werden informativ und
lesend als Ganzes zur Verfügung
gestellt.
Schwach strukturiertes
XML-Dokument
<x>
</x>
Sowohl beim Einfügen, Suchen
Ändern und Löschen werden oft
nur Teile der grossen Textblöcke
angesprochen. Die Abfragesprache muss gezielt Volltextfunktionen zur Verfügung stellen: Reguläre Mustersuche, NearAbfragen, Synonym-, Wortstamm-, Thesaurus-Suche,
boolsche Verknüpfungen usw.
Im Rahmen von XQuery stellt SQL Server 2005 lediglich SubstringSuchfunktion zur Verfügung (fn:contains() und fn:substring() ).
Jedoch gibt es ein contains-Prädikat im normalen SQL-Sprachumfang, das
alle Text-Datentypen und den XML-Datentyp volltextmässig behandeln
kann. Ein XML-Dokument wird dabei als ein einziges Volltext-Dokument
angesehen. Die Gliederung in einzelne Knoten bleibt unberücksichtigt. Das
contains-Prädikat ist sehr mächtig. Beispiele:
CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;
CREATE UNIQUE INDEX ix_pk ON Diplombeurteilung( idBeurteilung )
CREATE FULLTEXT INDEX ON Diplombeurteilung (beurteilung ) KEY INDEX ix_pk
select * from Diplombeurteilung
where contains( beurteilung, 'bocuse NEAR rüfenacht' )
–- NEAR heisst maximaler Abstand ?? Worte
select * from Diplombeurteilung
where contains( beurteilung, '"boc*" NEAR "rüf*"' )
select * from Diplombeurteilung
where contains( beurteilung, '"boc*" AND "rüf*"' )
select * from Diplombeurteilung
Technologie Memo
Arno Schmidhauser
8
XML in SQL Server 2005
where contains( beurteilung, 'FORMSOF( THESAURUS, allgemein ) ' )
-- Thesaurusliste ist konfigurierbar
7 Schema Evolution
Eine Schema-Collection kann mehrere Schemas aufnehmen. Ein XMLDokument muss einem dieser Schemas genügen. Es können jederzeit
neue Schemas in die Schema-Collection aufgenommen werden. Ein neues
XML-Dokument kann dann dem neuen oder einem der bestehenden
Schemas zugeordnet werden. Eine eigentliche Versionierung eines Schemas, im Sinne einer Typerweiterung, ist jedoch nicht möglich.
Die Schema-Definition in SQL-Server unterliegt ein paar Einschränkungen
gegenüber dem W3-Standard:
8 Schema Einschränkungen
SQL Server hat folgende Einschränkungen bezüglich XML Schema:
Item
Limitation/Restriction
SQL Server does not accept
sqltypes:datetime and sqltypes:smalldatetime sqltypes:datetime or sql:smalldatetime
values.
<xsd:include>, <xsd:key>, <xsd:keyref>,
SQL Server does not support these.
<xsd:redefine>, and <xsd:unique>
SQL Server rejects schemas that have an
<xsd:choice> particle without children,
<xsd:choice>
unless the particle is defined with a minOccurs attribute value of zero.
SQL Server only supports millisecond precision for simple types that have second
components and puts limitations on all
recognized XSD simple type enumerations.
<xsd:simpleType> values
SQL Server does not support using the
"NaN" value in <xsd:simpleType> declarations.
SQL Server ignores these attributes if they
are present in the XML instance data inserted into a column or variable of xml
data type
SQL Server does not support types derived
from xs:QName that use an XML Schema
restriction element.
xsi:schemaLocation and
xsi:noNamespaceSchemaLocation
xs:QName
Adding members to an existing substitution
group
Technologie Memo
SQL Server does not support union types
with xs:QName as a member element.
SQL Server does not support adding members to an existing substitution group in an
XML schema collection.
Arno Schmidhauser
9
XML in SQL Server 2005
Canonical forms
Enumeration facets
Facet length
ID attribute
ID type
List types and union types
Local namespace
Mixed type and simple content
Out-of-memory conditions
Repeated values
Schema component identifiers
Time zone information
Union types
Variable precision decimals
Technologie Memo
Canonical representation of a value cannot
violate the pattern restriction for its type.
SQL Server does not support XML schemas
with types that have pattern facets or enumerations that violate those facets.
SQL Server limits the range of acceptable
values for facet length.
XML schema components can have an ID
attribute, but SQL Server does not store
these values.
SQL Server does not support elements of
type xs:ID or xs:IDREF.
SQL Server does not support list types that
use union types as list items.
SQL Server rejects schemas that use an
empty string ("") as a value for the namespace attribute.
SQL Server does not support restricting a
mixed type to a simple content.
In working with large XML schema collections, an out-of-memory condition may
occur. Solutions are provided.
SQL Server rejects schemas in which the
block or final attribute has repeated values.
SQL Server limits identifiers of schema
components to a maximum length of 1000
Unicode characters. Also, surrogate character pairs within identifiers are not supported.
Time zone information is always normalized to Coordinated Universal Time
(Greenwich Mean Time).
SQL Server does not support restrictions
from union types.
SQL Server does not support variable precision decimals.
Arno Schmidhauser
10