Browse DevX
Sign up for e-mail newsletters from DevX


Store and Retrieve XML-based Business Objects with SQL Server and .NET : Page 3

Combine SQL Server's XML support and .NET XML serialization to simplify your front-end code by moving custom objects rather than DataSets between application layers and into and out of SQL Server.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Generate a Class to Store Query Results
There are two approaches for getting the XML returned by a query into class instances. First, if you set up the query XML to match the serialization format of an existing class that represents an order, you can deserialize the response of the GetOrderByID stored procedure directly into an instance of this class. If you don't have such a class already, it's fairly easy to build the class's definition manually, but there's a better option: Let the .NET framework generate the class's code for you. The XSD command-line tool can infer the schema of an existing XML file, as well as convert that schema into the appropriate .NET classes. Even better, you can request that SQL Server generate the schema of the data in addition to the XML data.

Step 1: Generate an XDR Schema. Modify the FOR XML AUTO clause by appending the XMLDATA option, which tells SQL Server to prefix its response with the definition of the schema that describes the structure of the data:


When you do that, SQL Server outputs the schema for the XML document enclosed in a tag. Physically, the schema appears before the actual data returned by the query. You can view the XML schema and data in the Query Analyzer's window by changing the Execute Mode to "Result in text." Set the maximum characters per column to 4,000 (the maximum value) on the Results tab of the Options dialog box. Figure 1 shows the Query Analyzer window after the execution of the GetOrderByID stored procedure.

Figure 1: The result of executing the GetOrderByID XML query in SQL Server's Query Analyzer with the FOR XML AUTO, XMLDATA option
Step 2: Save the XDR Schema. When you run the query, the result appears on a single long line, so the image doesn't show exactly what you'll see—I altered the result by formatting the XML document in the lower pane. That's purely cosmetic. SQL Server outputs an XDR (XML-Reduced Schema) schema format. You can copy the XML schema from the result pane and save it in a text file with the extension .xdr. Place the file in a folder with a short path name, because you will have to enter the file's complete path name in the next step. Listing 1 shows the schema returned by the GetOrderByID query.

Step 3: Transform the XDR schema into an XSD schema. Next, open a Command Prompt window and switch to the Bin folder under the Framework's SDK folder. In most installations this folder's path is C:\Program Files\Microsoft Visual Studio 2003\SDK\v1.1\Bin. In this folder you'll find the XSD.EXE command-line tool, which you can use to generate a class with the same structure as a specified schema.

Alternatively, you can open a Command Prompt window using the Command Prompt command from the Visual Studio .NET Tools submenu. When you do that, the XSD tool's location is part of the path and you can switch to the folder where you saved the XDR schema to process it. The following command converts the XDR schema into an XSD schema and saves it to a file in the current folder with the same name and extension XSD (you can also specify the output file name in the command's arguments), for example:

xsd c:\nworder.xdr

To learn about the syntax of the XSD utility, enter the name of the utility followed by a question mark in the Command Prompt window, and you'll see a list of all arguments and switches you can use with this tool:

xsd /?

Step 4: Generate a class that matches an XSD schema. Finally, you can use the same XSD.EXE tool to create a class with the same structure as the XSD schema you just created.

xsd nworder.xdr /classes /language:vb

Entering the preceding line at a command prompt causes the XSD utility to create a VB.NET class file called nworder.vb file, which contains the definition of a Schema1Order class that matches the schema of an order. Listing 2 shows the file contents.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date