belle’s sql musings

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

Posted in SQLXML, T-SQL Tips and Tricks by belle on October 13, 2008

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

xsd:int or xsd:integer


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

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

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


Microsoft also provided a list of schemas in It includes the following AdventureWorks sample schemas:

Tagged with: ,

Order of Attribute-Centric Columns in FOR XML PATH matters

Posted in Issues/Troubleshooting, SQLXML by belle on October 11, 2008


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


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

    EmployeeID       AS '@EmployeeID',            -- attribute
    FirstName        AS 'Details/FirstName',      -- nested element
    LastName         AS 'Details/LastName',       -- nested element
    Title            AS '@Title'                  -- attribute
FOR XML PATH('Employee')


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

    EmployeeID       AS '@EmployeeID',            -- attribute
    Title            AS '@Title',                 -- attribute
    FirstName        AS 'Details/FirstName',      -- nested element
    LastName         AS 'Details/LastName'        -- nested element
FOR XML PATH('Employee')

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

Tagged with:

Generating XSD from an XML File

Posted in SQLXML, Tools by belle on October 9, 2008

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.


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'.

The full options are as follows: (more…)

Tagged with: ,

Learning SQLXML on SQL Server 2005 – series of tutorials

Posted in SQLXML, Tutorials by belle on July 1, 2008

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

Jacob Sebastian’s XQuery Labs (

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.

Tagged with: