SQL 2005 Native XML Support

Andres Bohren

2009-04-27

Hallo zusammen,

Hier noch ein paar Demos zum XML Support in SQL Server 2005.

/****************************************************************************
* Zeigt die Verwendung von XML unter SQL Server 2005
* April 2009 Andres Bohren / http://blog.icewolf.ch
****************************************************************************/

/****************************************************************************
XML File
*****************************************************************************
<?xml version="1.0" encoding="ISO-8859-1"?>
<books>
<book daterated="2009-04-13">
  <title>Die Bancroft Strategie</title>
        <author>Robert Ludlum</author>
        <isbn>978-3-453-43364-9</isbn>
        <rating>4</rating>
</book>
    <book daterated="2009-03-26">
        <title>Die Hexengräfin</title>
        <author>Karla Weingand</author>
        <isbn>978-3-453-47079-8</isbn>
        <rating>4</rating>
    </book>
    <book daterated="2009-03-08">
        <title>Die Kastratin</title>
        <author>Iny Lorentz</author>
        <isbn>978-3-426-62366-4</isbn>
        <rating>5</rating>
    </book>
    <book daterated="2009-01-06">
        <title>Der Keller</title>
        <author>Richard Laymon</author>
        <isbn>978-3-453-43351-9</isbn>
        <rating>5</rating>
    </book>
    <book daterated="2008-09-29">
        <title>Kalte Asche</title>
        <author>Simon Beckett</author>
        <isbn>978-3-499-24195-6</isbn>
        <rating>4</rating>
    </book>
    <book daterated="2008-09-24">
        <title>Das Moskau Virus</title>
        <author>Robert Ludlum</author>
        <isbn>978-3-453-43062-4</isbn>
        <rating>4</rating>
    </book>
    <book daterated="2008-06-14">
        <title>Imperium</title>
        <author>Robert Harris</author>
        <isbn>978-3-453-47083-5</isbn>
        <rating>4</rating>
    </book>
</books>
****************************************************************************/

/****************************************************************************
*XSD File
****************************************************************************
<?xml version="1.0" encoding="iso-8859-1"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="books">
        <xs:complexType>
            <xs:sequence>
                <xs:element maxOccurs="unbounded" name="book">
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element name="title" type="xs:string" />
                            <xs:element name="author" type="xs:string" />
                            <xs:element name="isbn" type="xs:string" />
                            <xs:element name="rating" type="xs:unsignedByte" />
                        </xs:sequence>
                        <xs:attribute name="daterated" type="xs:date" use="required" />
                    </xs:complexType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>
****************************************************************************/

/*CREATE DATABASE*/
CREATE db_test

/*CREATE TABLE*/
USE db_test
GO
CREATE TABLE tBooks
(
fID INT PRIMARY KEY NOT NULL IDENTITY(1,1),
fBooks xml NOT NULL
)

/*CREATE XML SCHEMA*/
DECLARE @schema XML
SELECT @schema = myXSD FROM OPENROWSET(BULK 'D:\Data\Dokumente\MCSA\70-431\xml\books.xsd', SINGLE_BLOB)
AS TEMP(myXSD)
CREATE XML SCHEMA COLLECTION books AS @schema

/*LIST SCHEMAS*/
SELECT * FROM sys.xml_schema_collections

/*Load XML into SQL*/
SELECT CAST(x as XML) FROM OPENROWSET(BULK 'D:\Data\Dokumente\MCSA\70-431\xml\books.xml',SINGLE_BLOB) AS xmlFile(x)

/*IMPORT XML File into XML Column*/
INSERT INTO tBooks(fBooks)
SELECT CAST(x as XML) FROM OPENROWSET(BULK 'D:\Data\Dokumente\MCSA\70-431\xml\books.xml',SINGLE_BLOB) AS xmlFile(x)

/*CHECK IMPORT*/
SELECT * FROM tBooks

/*XQUERY*/
SELECT fBooks.query('/books/book/title') FROM tBooks

SELECT fBooks.value ('(/books/book/title/text())[1]', 'varchar(50)') AS fTitle,
fBooks.value ('(/books/book/author/text())[1]', 'varchar(50)') as fAuthor,
fBooks.value ('(/books/book/@daterated)[1]', 'datetime') as fDateRated
FROM tBooks

/*CREATE PRIMARY XML INDEX*/
CREATE PRIMARY XML INDEX idx_fBooks on tBooks(fBooks)

/*CREATE SECUNDARY XML INDEX (PATH, VALUE, PROPERTY)*/
CREATE XML INDEX idx_fBooks_PATH ON tBooks(fBooks) USING XML INDEX idx_fBooks FOR PATH
GO
CREATE XML INDEX idx_fBooks_VALUE ON tBooks(fBooks) USING XML INDEX idx_fBooks FOR VALUE
GO
CREATE XML INDEX idx_fBooks_PROPERTY ON tBooks(fBooks) USING XML INDEX idx_fBooks FOR PROPERTY
GO

/*SHOW INDEXES*/
SELECT * FROM sys.xml_indexes

/*RELATIONAL QUERY WITH XML OUTPUT*/
USE db_test
GO
select * from vKunden FOR XML AUTO,ROOT('personen')

select * from vKunden FOR XML RAW,ROOT('personen')
select * from vKunden FOR XML RAW, ROOT('personen'),ELEMENTS XSINIL;

select * from tKunden FOR XML PATH('personen')
select * from vKunden FOR XML PATH('person'),ROOT('doc')

Hier noch ein paar Links zum Thema:

Grüsse

Andres Bohren