SQL Server 2000 and XML For SQL Server 2000 Web Version (SQLXML) provides three ways to store XML data: XML Bulk Load and UpdateGrams, these two client technology uses an annotated outline to specify XML document content and database table Between mappings; OpenXML is a server-side technology that allows you to define a relationship view on an XML document. With OpenXML's relationship view, you can use T-SQL code to query the data in the XML document and store the results in Your SQL Server database. Each of these three technologies is designed for specific purposes. XML BULK LOAD stores data from a large XML document in SQL Server; UpdateGrams performs optimization updates for SQL Server data (optimized updates are unlocking updates, in which system detection is available in other users It turns out that after reading the data, it changes it); OpenXML provides people's familiar XML data relationship access methods. In these three technologies, OpenXML is the most flexible because it provides a programming model (T-SQL), where you can use this programming model to write a business in XML data before the XML data is stored in the SQL Server database. Rules or perform calculation logic. However, because OpenXML is a server-based technology, if you use it frequently or have a lot of documentation, it reduces the performance of SQL Server. However, if you use Microsoft .NET Framework Components, you can use ADO.NET's data set to open these performance and scalability, and ADO.NET datasets give you a powerful technology - in order to put XML Data Storage In SQL Server contains a complete programming model. Data sets, data tables and XML mappings You can generate XML query results from SQL Server using a data set. By providing a relational data buffer (Cache) that can be used on the client and intermediate layer computer, data can be loaded and maintained in multiple data sources (including SQL Server, other relational databases, and XML). When you load a dataset from the XML document, the data set must map the data stored in the hierarchical XML performance (representation) to the relationship of the data set. For example, if your XML document contains a list of ORDER elements, it has a nested LineItem element as a child element, then this document is usually mapped into the Orders and LineItems data tables in relational performance. The purpose of this mapping is the same as the method of using the relationship view of the relationship view on the XML document using the XPath query. But different from the XPATH specification, the data set has its own mapping data. Data Set Use the XML Outline Definition (XSD) Outline to map data from the XML document to the relational data cache of the dataset. The dataset provides you with two ways to specify the outline of mapped XML data. First, you can reference an XSD outline for elements, properties, and relationships that define the XML documents. Another way is that you can directly reason the outline from the structure of the document. In other words, the data set can establish an outline by checking the structure and content of the XML document. When you reference the XSD outline, the data set uses the element of the element defined in this outline to construct the data table, data column, and data relationships in the relational data cache, and you can use this data cache store to be mapped XML data. When I talk about the structure or outline in the relational data cache, I generally refer to it in the form of a data cache.
When the data set is handled, it applies a set of rules. This rule is similar to the default mapping rules used by UpdateGrams and XML Bulk Load when not specified in the mapping outline, and the data set uses this rule to create a data set for storage. A table of mapped XML data. The mapping rules for the data set are summarized as follows: • Composite elements - Elements containing other elements or properties are mapped to tables. • Simple-Valued Child Elements - Elements that contain only data, not containing other elements or attributes - mapping columns. • The data type is mapped from the XSD type to .NET type. Inference is a quick and convenient way to load an XML document into a data set. Tables, columns and relationships are automatically established by "INTROSPECTION", "self-measurement" is the process of the structure and content of the data set check XML document. Although the use of reasoning significantly reduces your programming burden, it also brings unpredictability to your implementation because small changes to XML documents may cause a data set to establish different forms of tables. These forms of changes may cause your application unexpected interrupt. Therefore, I recommend that you generally reference an outline for the application, and limit the use of reasoning when the prototype is established. Now let's take a look at how to build an example of client dataset data cache that can be used to update the SQL Server database. Mapping XML order assumes that you are writing an application that accepts user orders, the order is XML format, and its XSD outline is defined in Figure 1. The outline defines three composite types, providing customer data, order data, and linear data items for orders, respectively. A top Customer element defines the root of an XML document. This closed system defines the relationship between elements: The Order element contains a lineItem element, and the Customer element contains an Order element. Figure 2 shows an XML document instance that complies with the outline defined by Figure 1. Figure 1: XSD Outline
Listing 1: Creating a C # code of relational data cache
[quote]
Using system;
Using system.collections;
Using system.data;
Using system.data.sqlclient;
USING SYSTEM.XML;
Public Class XMLMAP
{
Public static void main ()
{
// Establish a data set and read outline
Dataset OrderDs = New Dataset
Custorder
");
ORDERDS.READXMLSCHEMA (
CustOrderLitem.xsd
");
// Print the form of the data set PrintDSShape (ORDERDS);
// read the order of an XML format into the data set
ORDERDS.READXML
ORDER.XML
", System.data.xmlreadmode.ignoreschema);
// Print the data in the data set
Printdsdata; OrderDS
// Insert business rules and database update logic here
}
Private Static Void Printdsshape (DataSet DS)
{
Foreach (DataTable DT in ds.tables)
{
Console.writeline
{0}
", DT.TABLENAME);
// Print column name and type
Foreach (Datacolumn DC in DT.columns)
Console.writeline
"/ t {0} / t {1}
", dc.columnname, dc.datatype.tostring ());
}
}
Private Static Void PrintData (Dataset DS)
{
Foreach (DataTable DT in ds.tables)
{
Console.writeline
"/ n {0}:
", DT.TABLENAME);
// Print the head of the 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
"
");
}
}
}
}
[/ quote]
Take a closer look at the name of the column. Although there is no Customer_ID and ORDER_ID columns in the outline, they still appear in the data table. READXMLSCHEMA automatically adds these columns to the dataset. The data set uses these columns as an external key to simulate the relationship between the Customer element between its Order elements, the ORDER element and its LineItem element. Because XML uses nested relationships in the typical case, the data set automatically generates its own primary key, the external key between the data tables, and stores them in these columns.
Carefully view the data type in Figure 3 - Data Sets have mapped data types from the XML outline data to the corresponding .NET data type. When you load the XML document into the data set, the data set converts each value from XML to the corresponding .NET type.
Figure 3: Generated data type and record
CustomerCustomerID System.StringCustomer_Id System.Int32OrderOrderID System.Int64Order_Id System.Int32Customer_Id System.Int32LineItemProductID System.Int32Quantity System.Int32UnitPrice System.DecimalOrder_Id System.Int32Customer: CustomerID Customer_IdALFKI 0Order: OrderID Order_Id Customer_Id10966 0 0LineItem: ProductID Quantity UnitPrice Order_Id37 8 26.5 056 12 38 0
After loading the big outline into the data set, in order to complete the relationship map, all things you need to do is to load XML data into the dataset. Listing 1 The READXML method opens the file called Order.xml, which is shown in Figure 2. Then, it reads the data in the file to the data sheet you just read the data set established by the outline. Your XML order can now be accessed through a dataset. In order to demonstrate how to access data in the data set, the list 1 of the list 1 navigates in the data table. For each table, the name of the column is displayed, followed by displaying all the rows of this table. Figure 3 shows the Customer_ID and ORDER_ID columns added to the readxmlschema method to automatically generate values.
Please note that three elements that appear in Order.xml --PO, Address, and DE
Scription - not mapped to the data table. These data is ignored because the outline you provide to the dataset does not contain these elements, and when the data set establishes the shape of the relational data cache and loads XML data, it simply ignores the data not described in the outline. Even in the XML order you have received from the customer contains uniserated additional data, this simple feature can also make your code work normally.
Establish an application using a data cache
Now you know how to use the dataset to establish a relational data cache for XML data, you can apply this technique to implement an application that performs business logic and updates SQL Server applications. The business logic is relatively straightforward when you use the data set programming model. ADO.NET provides you with several options for updating data in SQL Server, including using data adapters, writing your own queries, and performing stored procedures. Data Element Map the XML data into a relational model is easy, and the remaining things are yours.
