using xml in sql server 2005 nametitlecompany. xml overview business opportunity the majority of all...

Using XML in SQL Server 2005Using XML in SQL Server 2005

NameNameTitleTitleCompanyCompany

XML OverviewXML OverviewBusiness OpportunityBusiness Opportunity

The majority of all data transmitted The majority of all data transmitted electronically between organizations electronically between organizations is now in XML. (source this besides is now in XML. (source this besides ebXML)ebXML)

By storing XML natively, SQL Server By storing XML natively, SQL Server 2005 helps eliminate the friction of 2005 helps eliminate the friction of data transfer between organizations.data transfer between organizations.

With the improved XML support in VS With the improved XML support in VS 2005, building e-business 2005, building e-business applications is radically simplified.applications is radically simplified.

XML in the DatabaseXML in the DatabaseOverviewOverview

XML has become widely adoptedXML has become widely adopted

XML is ubiquitous, extensible, and a XML is ubiquitous, extensible, and a platform independent transport platform independent transport formatformat

Supports structured and semi-Supports structured and semi-structured datastructured data

Limited support in SQL Server 2000Limited support in SQL Server 2000

First class support in SQL Server First class support in SQL Server 20052005

XML in the DatabaseXML in the DatabaseXML Data TypeXML Data Type

SQL Server 2005 adds native XML SQL Server 2005 adds native XML storage with XML data typestorage with XML data type

Stores both well-formed and validated Stores both well-formed and validated XMLXML

ImplementationImplementationMarkup documentsMarkup documents

Tree-structured data where re-assembly Tree-structured data where re-assembly costs should be minimalcosts should be minimal

Semi-structured dataSemi-structured data


An alternative to storing XML on diskAn alternative to storing XML on disk

Supports column, variable, or Supports column, variable, or parameterparameter

Can represent Version 1.0 documents Can represent Version 1.0 documents or fragmentsor fragments

Methods on XML data typeMethods on XML data typequery(), value(), exist(), modify(), and query(), value(), exist(), modify(), and nodes()nodes()


Table structure storing XML as CLOBTable structure storing XML as CLOB

CREATE TABLE [JobCandidate](CREATE TABLE [JobCandidate](

[JobCandidateID] [int] IDENTITY(1,1) NOT NULL,[JobCandidateID] [int] IDENTITY(1,1) NOT NULL,

[EmployeeID] [int] NULL,[EmployeeID] [int] NULL,

[Resume] [ntext] NULL,[Resume] [ntext] NULL,

[ModifiedDate] [datetime] NOT NULL DEFAULT GetDate())[ModifiedDate] [datetime] NOT NULL DEFAULT GetDate())

CLOB = Character Large ObjectCLOB = Character Large ObjectTEXT, NTEXT, VARCHAR, NVARCHAR, TEXT, NTEXT, VARCHAR, NVARCHAR, etc.etc.


Table structure storing XML as XML!Table structure storing XML as XML!




[Resume] [Resume] [xml][xml] NULL, NULL,



XML columns can have CHECK XML columns can have CHECK Constraints like a relational columnConstraints like a relational column




[Resume] [xml] NULL [Resume] [xml] NULL

CHECK (Resume.exist('//school/college') = 1),CHECK (Resume.exist('//school/college') = 1),


XML Data TypeXML Data Type

XML in the DatabaseXML in the DatabaseXML Schema SupportXML Schema Support

Validation and typing provided by XSDValidation and typing provided by XSDW3C recommendationW3C recommendation

Mechanism for type definitionsMechanism for type definitions

Validation constraintsValidation constraints

Typed DataTyped DataGuarantees the Guarantees the shapeshape of the data of the data

Allows storage and query optimizationsAllows storage and query optimizations

XML in the DatabaseXML in the DatabaseXML Schema SupportXML Schema Support

Associating a SchemaAssociating a Schema

CREATE XML SCHEMA COLLECTION ResumeSchema AS ...CREATE XML SCHEMA COLLECTION ResumeSchema AS ...




[Resume] [xml] [Resume] [xml] ([ResumeSchema])([ResumeSchema]) NULL, NULL,


XML in the DatabaseXML in the DatabaseXML Schema CollectionsXML Schema Collections

Creating an XML Schema CollectionCreating an XML Schema CollectionCREATE XML SCHEMA COLLECTION ResumeSchema ASCREATE XML SCHEMA COLLECTION ResumeSchema AS

'<xs:schema '<xs:schema

targetNameSpace='http://schemas.ms.com/emp" …/>'targetNameSpace='http://schemas.ms.com/emp" …/>'

Constraining XML documentsConstraining XML documentsCREATE TABLE Employee (..., Resume XML(ResumeSchema))CREATE TABLE Employee (..., Resume XML(ResumeSchema))

Retrieving XML Schema CollectionsRetrieving XML Schema CollectionsSELECT NameSELECT Name

FROM AdventureWorks.sys.xml_schema_collectionsFROM AdventureWorks.sys.xml_schema_collections

Associating a SchemaAssociating a Schema

XML in the DatabaseXML in the DatabaseXML IndexesXML Indexes

Create XML index on XML columnCreate XML index on XML columnCREATE PRIMARY XML INDEX IDX1 ON JobCandidate (Resume)CREATE PRIMARY XML INDEX IDX1 ON JobCandidate (Resume)

Creates a secondary, B-tree index onCreates a secondary, B-tree index onPath, Property, or ValuePath, Property, or Value

Speeds up queriesSpeeds up queriesResults can be served directly from indexResults can be served directly from index

Uses SQL Server's cost based optimizerUses SQL Server's cost based optimizer

Full-Text Indexing/Searching supports Full-Text Indexing/Searching supports XMLXML


PATH IndexPATH IndexWhen querying by path and valueWhen querying by path and value

/ItemList/Item[@ProductID="1"]/ItemList/Item[@ProductID="1"]

PROPERTY IndexPROPERTY IndexWhen retrieving node values by pathWhen retrieving node values by path

(/ItemList/Item@ProductID)[1](/ItemList/Item@ProductID)[1]

VALUE IndexVALUE IndexWhen querying imprecisely, like for existenceWhen querying imprecisely, like for existence

//Item[@ProductID="1"]//Item[@ProductID="1"]


RequirementsRequirementsA clustered PK must exist in the tableA clustered PK must exist in the table

If XML index exists, the PK may not be If XML index exists, the PK may not be alteredaltered

Only one primary XML index per columnOnly one primary XML index per column

Many secondary XML indexes per columnMany secondary XML indexes per columnPATH, PROPERTY, and VALUEPATH, PROPERTY, and VALUE

All index names must be unique per tableAll index names must be unique per table

XML in the DatabaseXML in the DatabaseXQueryXQuery

Query XML documents and dataQuery XML documents and dataBased on W3C working draftBased on W3C working draft

FLWOR - For, Let, Where, Order by, ReturnFLWOR - For, Let, Where, Order by, Return

XQuery methodsXQuery methods.query() – returns XML data type instance.query() – returns XML data type instance

.value() – returns a single (scalar) value.value() – returns a single (scalar) value

.exist() – returns 1 if expression returns .exist() – returns 1 if expression returns item(s)item(s)

.nodes() – returns a row per selected node.nodes() – returns a row per selected node


XQuery .query() exampleXQuery .query() example

// Search for an ID of 123 and return section// Search for an ID of 123 and return section

// heading of section 3 and later// heading of section 3 and later

SELECT ID, xmlColumn.query('for $s inSELECT ID, xmlColumn.query('for $s in

/doc[@id = 123]//sec[@num >= 3]/doc[@id = 123]//sec[@num >= 3]

return return <topic>{data($s/heading)}</topic>') <topic>{data($s/heading)}</topic>') FROM xmlTableFROM xmlTable


XQuery .value() exampleXQuery .value() example

// Extracts an XQuery value into the SQL// Extracts an XQuery value into the SQL

// value and type space// value and type space

SELECT xmlColumn.value(SELECT xmlColumn.value(

'(/doc[@id = 123]//sec[@num >= 3]/@num)[1]','(/doc[@id = 123]//sec[@num >= 3]/@num)[1]',

'int')'int')

FROM xmlTableFROM xmlTable


XQuery .exist() exampleXQuery .exist() example

// Return a 1 if the XQuery expression returns// Return a 1 if the XQuery expression returns

// at least one item, 0 otherwise// at least one item, 0 otherwise

SELECT * FROM xmlTableSELECT * FROM xmlTable

WHERE xmlColumn.exist('/a/b[@id=42]') = 1WHERE xmlColumn.exist('/a/b[@id=42]') = 1

XQueryXQuery

XML in the DatabaseXML in the DatabaseXML Data Manipulation LanguageXML Data Manipulation Language

Inefficient to UPDATE the entire Inefficient to UPDATE the entire columncolumn

XML sub-tree modification XML sub-tree modification using using .modify().modify()

Three actions: Three actions: InsertInsert, , DeleteDelete, or , or ReplaceReplace

UPDATE xmlTable SET xmlColumn.modify('insert UPDATE xmlTable SET xmlColumn.modify('insert

<section num="2"><section num="2">

<heading>Background</heading><heading>Background</heading>

</section></section>

after (/doc/section[@num=1])[1]')after (/doc/section[@num=1])[1]')

XML/DMLXML/DML

XML in the DatabaseXML in the DatabaseFOR XML ImprovementsFOR XML Improvements

Support for XSD schemasSupport for XSD schemas

Support for NULL valuesSupport for NULL values

Support for XML data typeSupport for XML data type

Nested FOR XMLNested FOR XML

Can be assigned to an XML data typeCan be assigned to an XML data type

FOR XML PATHFOR XML PATH

FOR XML ImprovementsFOR XML Improvements

XML in the DatabaseXML in the DatabaseSummarySummary

XML Data TypeXML Data Type

XML Schema SupportXML Schema Support

XML IndexesXML Indexes

XQueryXQuery

XML Data Manipulation LanguageXML Data Manipulation Language

FOR XML ImprovementsFOR XML Improvements

ResourcesResources

On the WebOn the WebWorld Wide Web ConsortiumWorld Wide Web Consortium

http://www.w3.orghttp://www.w3.org

W3 SchoolsW3 Schoolshttp://www.w3schools.comhttp://www.w3schools.com

http://www.w3.org/

http://www.w3schools.com/

using xml in sql server 2005 nametitlecompany. xml overview business opportunity the majority of all...

Documents