Microsoft® SQL Server® 2012 Bible

347

Chapter 14: Using XML Data

14

The great thing is that you don’t need to learn a new language because Microsoft has cre-

ated a nifty little tool to generate the schema for you based on the XML you want to vali-

date. Walk through an example.

Open up your favorite text editor (Notepad works just fi ne) and type in the following XML:

<Order OrderID="1">

<Item>

<ItemNumber>V001</ItemNumber>

<Quantity>1</Quantity>

<Price>299.99</Price>

</Item>

</Order>

Save the text fi le as orderxml.xml. With the XML document created, you can use a nifty

little tool to create the schema based on the XML document you just created. The tool is

called the XML Schema Defi nition Tool (XSD.exe go fi gure) and generates XML schema or

even Common Language Runtime (CLR) classes from XDR, XML, and XML fi les. You can fi nd

this tool in the following location:

C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\x64

Open a command prompt, and navigate the preceding directory. The syntax for creating the

schema is simple:

Xsd.exefilename.xml /outputdirectory /parameters

To create the schema, you need to pass the path to the fi le you previously created, and

the path and name of the new XSD schema you want to create. You can optionally supply a

number of parameters.

In your command prompt, type the following (assuming you saved your XML fi le in C:\Temp):

Xsd.exe c:\temp\orderxml.xml /outputdir:c:\temp

When specifying the output directory, you need to specify only the directory, as shown in

the preceding code. If you leave off the actual name of the fi le, it uses the name of the XML

fi le as the name of the XSD fi le.

Press the Enter key after you have typed it in, and quickly you notice a new fi le in the

same directory as your XML fi le. Open the orderxml.xsd fi le to look at its contents.

Intricate syntax isn’t covered here, but what you see is the validation of your XML, and this

is what you create your XML Schema Collection from.

So, copy all the contents of the orderxml.xsd fi le to the clipboard, and create a new

query window in SQL Server Management Studio, pasting in the contents of the

clipboard. To create the XML Schema Collection, you simply need to add the

CREATE XML SCHEMA COLLECTION statement to the beginning of the schema,

as shown here:

c14.indd 347c14.indd 347 7/30/2012 4:49:00 PM7/30/2012 4:49:00 PM

http://www.it-ebooks.info