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

Advertisements
Tagged with: ,

Order of Attribute-Centric Columns in FOR XML PATH matters

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

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>
*/

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.

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: (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 (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.

Tagged with: