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! :)

VN:F [1.9.22_1171]
Rating: 8.7/10 (3 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

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)

VN:F [1.9.22_1171]
Rating: 8.8/10 (5 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

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

 

Note that in SQL Server 2005, the timezone needs to be included in the date or dateTime element or attribute value, for example:

<Authors>
    <Author AuthorID="1" FirstName="John" LastName="Doe" 
            IsIndependent="true" 
            DateJoined="1992-05-01Z" DateTimeJoined="1992-05-01T00:00:00Z"/>
</Authors>

Note the existence of the letter Z at the end of the date and datetime attributes.

 

Microsoft also provided a list of schemas in http://schemas.microsoft.com/sqlserver/. It includes the following AdventureWorks sample schemas:

  • AdventureWorks Customer Contact Information Schema
  • AdventureWorks Customer Contact Record Schema
  • AdventureWorks Customer Contact Type Schema
  • AdventureWorks Consumer Demographics Survey Schema
  • AdventureWorks Product Catalog Schema
  • AdventureWorks Manufacturing Instructions Document Schema
  • AdventureWorks Product Warranty and Maintenance Information Schema
  • AdventureWorks Standard Resume Schema
  • AdventureWorks Retail Stores Demographics Survey Schema
VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)
`