Use. NET stores XML data

xml| data SQL Server 2000 and XML for SQL Server Web version (SQLXML) provides three ways to store XML data: XML Bulk load and Updategrams, These two client technologies use an outline with annotations to specify the mapping between the contents of the XML document and the tables of the database; OpenXML is a server-side technology that allows you to define a relational view on an XML document with a OPENXML relational view, You can use T-SQL code to query the data in an XML document and store the results in your SQL Server database.

Each of these three technologies is designed for a specific purpose. XML Bulk load stores data from very large XML documents in SQL Server; Updategrams perform an optimized update of SQL Server data (optimized update is a lock-free update. In this update, the system detects if another user has changed it after the data was originally read; OpenXML provides a familiar access method for XML data relations.

Of these three technologies, OPENXML is the most flexible because it provides a programming model (T-SQL) that you can use to write business rules or perform computational logic on XML data before storing the XML data in a SQL Server database. However, since OPENXML is a server-based technology, it can degrade SQL Server performance if you use it frequently or if you have a large number of documents. However, if you are using Microsoft. NET Framework components, you can use ado.net datasets to circumvent these performance and scalability constraints, ado.net datasets give you a powerful technology--a complete programming model for storing XML data in SQL Server.

datasets, datasheets, and XML mappings

You can use datasets to simply generate XML query results from SQL Server. A dataset can load and maintain data from a variety of data sources, including SQL Server, other relational databases, and XML, by providing a relational data cache (cache) that can be used on both client and middle-tier computers.

When you load a dataset from an XML document, the dataset must map the data stored in the hierarchical XML representation (representation) to the relational representation of the DataSet. For example, if your XML document contains a list of order elements, and it has a nested lineitem element as a child element, this document is usually mapped to orders and LineItems data tables in the relational representation. The purpose of this mapping is the same as the way OPENXML uses XPath queries to construct relational views on XML documents. However, unlike using XPath specifications, datasets have their own way of mapping data.

Datasets use an XML outline definition (XSD) outline to map data from an XML document to a relational data cache in a dataset. A dataset gives you two ways to specify an outline that maps XML data. First, you can refer to an XSD outline that defines the elements, attributes, and relationships that are used in an XML document. Alternatively, you can infer the outline directly from the structure of the document. In other words, a dataset can build an outline by examining the structure and content of an XML document.

When you refer to an XSD outline, the dataset uses the elements defined in the outline, the relationships between attributes and elements to construct the data tables, data columns, and data relationships in the relational data cache, which you can use to store the mapped XML data. When I talk about structures or outlines in relational data caching, it's generally called the form of data caching. When a dataset processes an outline, it applies a set of rules similar to the default mapping rules used by Updategrams and XML Bulk load when no annotations are specified in the mapping outline, which is used by the dataset to establish the table where the dataset is used to store the mapped XML data. The mapping rules for datasets are summarized as follows:

。 Composite elements-that is, elements that contain other elements or attributes-are mapped into tables.

。 Attributes and simple values (simple-valued) child elements--elements that contain only data and do not contain other elements or attributes--are mapped to columns.

。 Data types are mapped from XSD types to. NET type.

Inference (inference) is a fast and convenient way to load an XML document into a dataset. tables, columns, and relationships are automatically established by self measurement (introspection), which is the process by which datasets examine the structure and content of XML documents. Although using inference significantly reduces your programming burden, it also brings unpredictability to your implementation, because small changes to XML documents can cause datasets to create different forms of tables. These forms of change may cause your application to break unexpectedly. Therefore, I recommend that you generally refer to an outline for your application to limit the use of inference when building prototypes.

Now let's take a look at how to use the outline to create an example of a client dataset data cache that can be used to update a SQL Server database.

Map XML Orders

Suppose you are writing an application that accepts a user's order, the order is in XML format, and its XSD outline is defined in Figure 1. The outline defines three composite types that provide customer data for orders, order data, and linear data items. A top-level customer element defines the root of an XML document. This closed system defines the relationships between elements: The order element contains a LINEITEM element, and the customer element contains a single element. Figure 2 shows an XML document instance that conforms to the outline defined in Figure 1.

Figure 1:xsd Outline

xmlns=" urn:sep2003example "
xmlns:xs=" http://www.w3.org/2001/xmlschema ">

Figure 2: An example of an XML document

>
ALFKI
9572658

One Main Street
Anywhere
NJ
08080

10966

Notoginseng
26.50
8
Gravad LAX


All
38.00

gnocchi di Nonna Alice

The C # code shown in Listing 1 uses the ReadXmlSchema method to load the outline in Figure 1 into a dataset called Orderds. ReadXmlSchema establishes three data tables that correspond to the customer, order, and lineitem elements defined in the outline. So you can verify that the outline creates the expected table in the relational data cache, and the Printdsshape method writes the name of each table to the console followed by the list of columns and the data type of each column.

Listing 1: C # code to establish a relational data cache

Using System;
Using System.Collections;
Using System.Data;
Using System.Data.SqlClient;
Using System.Xml;

public class XMLMap
{
public static void Main ()
{

Set up datasets and read outlines
DataSet orderds = new DataSet ("CustOrder");
Orderds.readxmlschema ("custorderlitem.xsd");

Print the form of a dataset
Printdsshape (orderds);

Read an XML-formatted order into the dataset
Orderds.readxml ("Order.xml", System.Data.XmlReadMode.IgnoreSchema);

Print data in a dataset
Printdsdata (orderds);

Business rules and database update logic is inserted here
}

private static void Printdsshape (DataSet ds)
{
foreach (DataTable dt in DS. Tables)
{
Console.WriteLine ("{0}", dt.) TableName);

Print the name and type of the column
foreach (DataColumn dc in dt. Columns)
Console.WriteLine ("\t{0}\t{1}", DC. COLUMNNAME,DC. Datatype.tostring ());
}
}

private static void Printdsdata (DataSet ds)
{
foreach (DataTable dt in DS. Tables)
{
Console.WriteLine ("\n{0}:", dt. TableName);

Print the header of a column
foreach (DataColumn dc in dt. Columns)
Console.Write ("{0}\t", DC.) ColumnName);
Console.WriteLine ("");

Output data
foreach (DataRow dr in Dt. Rows)
{
foreach (DataColumn dc in dt. Columns)
System.Console.Write ("{0}\t", DR[DC]);
System.Console.WriteLine ("");
}
}
}
}

Take a closer look at the name of the following. Although customer_id and order_id columns are not specified in the outline, they appear in the datasheet. ReadXmlSchema add these columns to the dataset automatically. Datasets use these columns as foreign keys (foreign key) to simulate the relationship between the customer element and its order element, the order element, and its lineitem element. Because XML typically replaces the foreign key with nested relationships, the dataset automatically generates its own primary key, the foreign keys between the data tables, and stores them in these columns.

Look at the data type in Figure 3 at the same time-the dataset has mapped the data types from the XML outline data types to the corresponding. NET data types. When you load an XML document into a dataset, the dataset converts each value from XML to the corresponding. NET type.

Figure 3: Generated data types and records

Customer
CustomerID System.String
customer_id System.Int32

Order
OrderID System.Int64
order_id System.Int32
customer_id System.Int32

LineItem
ProductID System.Int32
Quantity System.Int32
UnitPrice System.Decimal
order_id System.Int32

Customer:
CustomerID customer_id
ALFKI 0

Order:
OrderID order_id customer_id
10966 0 0

LineItem:
ProductID Quantity UnitPrice order_id
37 8 26.5 0
56 12 38 0

After loading the outline into the dataset, all you need to do to complete the relationship mapping is to load the XML data into the dataset. The ReadXml method in Listing 1 opens the file called Order.xml, as shown in Figure 2. It then reads the data in the file to the data table in the dataset that you just read the outline. Your XML order can now be accessed through the dataset.

To demonstrate how to access data in a dataset, the Printdsdata method of listing 1 navigates through the datasheet, displaying the name of the column for each table, followed by the display of all rows of the table. Figure 3 shows that the customer_id and order_id columns added to the dataset by the ReadXmlSchema method automatically generate a value.

Note that the three elements--po, address, and description--that appear in Order.xml are not mapped to the datasheet. This data is ignored because the outline you provide to the dataset does not contain these elements, and it simply ignores the data that is not described in the outline when the dataset establishes the shape of the relational data cache and loads the XML data. Even if the XML order you receive from the customer contains unexpected additional data, this simple feature will also allow your code to work properly.

Building an application that uses data caching

Now that you know how to use datasets to create relational data caches for XML data, you can apply this technique to implement an application that performs business logic and updates SQL Server. When you use a dataset programming model, it is relatively straightforward to implement the business logic. Ado. NET gives you several options for updating data in SQL Server, including using data adapters, writing your own queries, and executing stored procedures. Datasets make it easy to map XML data into relational models, and the rest of the stuff is yours.