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

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.

Reason:

You are having this issue if you have attribute- and element-centric columns in your FOR XML PATH query that are interleaved, as such:

SELECT

    EmployeeID       AS '@EmployeeID',            -- attribute

    FirstName        AS 'Details/FirstName',      -- nested element

    LastName         AS 'Details/LastName',       -- nested element

    Title            AS '@Title'                  -- attribute

FROM

    dbo.Employee

FOR XML PATH('Employee')

Resolution:

Order of attribute-centric columns in your FOR XML PATH query matters … if you have nested elements.

To resolve the error above, you must specify all attributes first, then specify the nested elements

SELECT

    EmployeeID       AS '@EmployeeID',            -- attribute

    Title            AS '@Title',                 -- attribute

    FirstName        AS 'Details/FirstName',      -- nested element

    LastName         AS 'Details/LastName'        -- nested element

FROM

    dbo.Employee

FOR XML PATH('Employee')

/*

Sample Output:

<Employee EmployeeID="1" Title="Production Technician - WC60">

  <Details>

    <FirstName>Guy</FirstName>

    <LastName>Gilbert</LastName>

  </Details>

</Employee>

*/

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 Directory\Microsoft Visual Studio 8\SDK\v2.0\Bin). 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:\temp\invoice.xsd'.

C:\temp>

The full options are as follows:

Learning SQLXML on SQL Server 2005 – series of tutorials

Leaning how to manipulate XML in SQL Server 2005? You have to read

Jacob Sebastian’s XQuery Labs (http://www.sqlserverandxml.com/search/label/XQuery%20Lab)

Jacob has done a wonderful job compiling common how-to’s when working with SQLXML. Must read for anyone wanting to move faster with SQLXML.