SQL Server XML Red Gate Ebook, XQuery Labs

Jacob Sebastian is a SQL Server XML Guru!

Check out his collection for XQUery Labs. This is the first 12 of his series, and right now he has 43 and counting:

XQuery Sample Scripts

* XQuery Lab 1 – Transforming rows to columns
* XQuery Lab 2 – An example using OUTER APPLY
* XQuery Lab 3 – Filtering specific nodes
* XQuery Lab 4 – Joining XML Nodes with a Relational Table
* XQuery Lab 5 – Working with Namespaces
* XQuery Lab 6 – Processing Header-Detail information
* XQuery Lab 7 – Extracting a comma separated list of values
* XQuery Lab 8 – How to update the attribute value of an XML variable?
* XQuery Lab 9 – How to delete an attribute from an XML variable?
* XQuery Lab 10 – How to insert an attribute to an XML variable
* XQuery Lab 11 – How to insert an element to an XML variable
* XQuery Lab 12 – Different ways of reading values from an XML variable

Jacob Sebastian has also released a free ebook via RedGate – The Art of XSD – SQL Server XML Schema Collections

Check it out, all 483 pages! 🙂

SQLXML : How to Work With XML Elements (or Nodes) in SQL Server

Assume this is your XML snippet
[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

DECLARE @authorsXML XML

SET @authorsXML = ‘
<Author>
<ID>172-32-1176</ID>
<LastName>White</LastName>
<FirstName>Johnson</FirstName>
<Address>
<Street>10932 Bigge Rd.</Street>
<City>Menlo Park</City>
<State>CA</State>
</Address>
</Author>
‘

[/sql]

Note that the examples below show how you can manipulate XML nodes – but most operations require singleton values. Ie, the changes must affect one and only one node. Thus in most the examples we specify the index of the node we want to target.

For example:

[sql collapse=”false” firstline=”1″ gutter=”false” smarttabs=”true” tabsize=”4″ toolbar=”false”]

(/Author/LastName)[1]

[/sql]

which means we are only targetting the first instance of LastName under the Author node. If you need to do a mass update, you may need to use a cursor.

SQLXML : How to Alter Existing Column Schema (XSD)

Sometimes you may need to change schema definitions in existing columns.

The following shows an example of how you can do this:

   1: -- drop the XML schema from existing column definitions

   2: ALTER TABLE AD

   3:    ALTER COLUMN Title xml

   4:  

   5: -- DROP IF EXISTS

   6: IF    EXISTS (SELECT name 

   7:               FROM  sys.xml_schema_collections 

   8:               WHERE name='TitleSchema')

   9:    DROP  XML SCHEMA COLLECTION TitleSchema

  10: GO

  11:  

  12: -- new definition

  13: -- there can only be one Title element

  14: CREATE XML SCHEMA COLLECTION TitleSchema AS 

  15: '<?xml version="1.0" encoding="utf-8"?>

  16: <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">

  17:   <xsd:element name="Title">

  18:     <xsd:complexType>

  19:          <xsd:attribute name="Type" type="xsd:string" />

  20:          <xsd:attribute name="Value" type="xsd:string" use="optional" />

  21:     </xsd:complexType>

  22:   </xsd:element>

  23: </xsd:schema>'

  24: GO

  25:  

  26: -- IMPORTANT: before you add back the schema to the column 

  27: -- definition, make sure all values in your existing column 

  28: -- comply with the new schema definition

  29: ALTER TABLE AD

  30:    ALTER COLUMN Title xml(TitleSchema)

SQLXML : How to List Columns That Have the XML Data Type

   1: -- this lists the table catalog, table name, 

   2: -- column name, and data type

   3: SELECT 

   4:     TABLE_CATALOG,

   5:     TABLE_NAME,

   6:     COLUMN_NAME, 

   7:     DATA_TYPE 

   8: FROM 

   9:     INFORMATION_SCHEMA.COLUMNS

  10: WHERE 

  11:     DATA_TYPE = 'xml'

  12:  

 

   1: -- this lists the corresponding schemas

   2: SELECT 

   3:     DISTINCT

   4:     OBJECT_NAME(sys.columns.object_id)        AS 'TableName',

   5:     sys.columns.name                    AS 'ColName',

   6:     sys.xml_schema_collections.name            AS 'Schema' 

   7: FROM 

   8:     sys.columns

   9:     LEFT JOIN     sys.xml_schema_collections 

  10:     ON sys.columns.xml_collection_id = sys.xml_schema_collections.xml_collection_id

  11: ORDER BY 

  12:     OBJECT_NAME(sys.columns.object_id), sys.columns.name    

  13:  

SQLXML : How to List Schema Elements and Attributes

   1: SELECT

   2:     sys.xml_schema_collections.xml_collection_id    AS CollectionID,

   3:     sys.xml_schema_collections.name                 AS SchemaName,

   4:     sys.xml_schema_elements.name                    AS ElementName,

   5:     sys.xml_schema_attributes.name                  AS AttributeName

   6: FROM

   7:     sys.xml_schema_collections

   8:     INNER JOIN sys.xml_schema_attributes

   9:     ON sys.xml_schema_collections.xml_collection_id =  sys.xml_schema_attributes.xml_collection_id

  10:     INNER JOIN sys.xml_schema_elements

  11:     ON sys.xml_schema_collections.xml_collection_id = sys.xml_schema_elements.xml_collection_id

  12: WHERE

  13:     sys.xml_schema_collections.name NOT LIKE 'sys'

Sample Result:

SQLXML : How to Create an XML Schema

The following is an example of how to create an XML Schema in SQL Server.

   1: -- DROP IF EXISTS

   2: IF    EXISTS (SELECT 1 

   3:               FROM  sys.xml_schema_collections 

   4:               WHERE name='SampleSchema')

   5:    DROP XML SCHEMA COLLECTION SampleSchema

   6:  

   7: CREATE XML SCHEMA COLLECTION SampleSchema AS 

   8: '<?xml version="1.0" encoding="utf-8"?>

   9: <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">

  10:   <xsd:element name="Sample">

  11:     <xsd:complexType>

  12:       <xsd:attribute name="SampleID" type="xsd:integer" />

  13:       <xsd:attribute name="Name" type="xsd:string" />

  14:       <xsd:attribute name="Description" type="xsd:string" />

  15:     </xsd:complexType>

  16:   </xsd:element>

  17: </xsd:schema>'

 

To check your XML Schema:

Method 1: Go to your database > Programmability > Types > XML Schema Collections

Method 2: You can use the xml_schema_namespace function to query the schema from within SSMS

   1:  

   2: SELECT

   3:    xml_schema_namespace(N'dbo',N'SampleSchema') 

 

Later on I will post additional sample schemas which use different SQL XML data types.

SQLXML : How to Check if an XML Schema Already Exists

   1: SELECT * 

   2: FROM  sys.xml_schema_collections 

   3: WHERE name='NameOfSchema'

Valid SQLXML XSD Data Types, and Sample SQL Server XML Schemas

Here is a partial list of valid XSD data types for SQL Server 2005:

xsd:int or xsd:integer

xsd:decimal

xsd:date

xsd:dateTime

xsd:string

xsd:base64Binary

Order of Attribute-Centric Columns in FOR XML PATH matters

Error:

Attribute-centric column ‘@Title’ must not come after a  non-attribute-centric sibling in XML hierarchy in FOR XML PATH.

Generating XSD from an XML File

To create a schema (XSD) from an XML file, you can use a command line tool that comes with Visual Studio called xsd.exe (located in Install DirectoryMicrosoft Visual Studio 8SDKv2.0Bin). If you’re running Visual Studio 2008, your path will be different.

Usage:

xsd.exe -

    Utility to generate schema or class files from given source.

xsd.exe <schema>.xsd /classes|dataset [/e:] [/l:] [/n:] [/o:] [/s] [/uri:]

xsd.exe <assembly>.dll|.exe [/outputdir:] [/type: [...]]

xsd.exe <instance>.xml [/outputdir:]

xsd.exe <schema>.xdr [/outputdir:]

Sample invocation:

C:temp>xsd invoice.xml

Microsoft (R) Xml Schemas/DataTypes support utility

[Microsoft (R) .NET Framework, Version 2.0.50727.42]

Copyright (C) Microsoft Corporation. All rights reserved.

Writing file 'C:tempinvoice.xsd'.

C:temp>

The full options are as follows: