http://www.microsoft.com/china/msdn/library/data/sqlser/sqlxmlado.mspx
Release Date: 12/6/2004
| Update Date: 12/6/2004
Bob BeautyMindevelopmentor
Suitable for: Microsoft SQL Server 2005microsoft ADO.NET 2.0XML
Summary: View improvements in XML support in Microsoft ADO.NET 2.0 How to work with Microsoft SQL Server 2005 to make XML data in the process application easier.
This page
The introduction is XML or string? Documents, fragments and for XML support to support small knops on the client
Introduction
One of the major changes in Microsoft SQL Server 2005 is to include the XML data type. This data type is the first type, just like int or varchar, and SQL Server 2005 allows the use of a series of XML-specific functions to perform local query and processing. It also supports the collection of XML architectures in the database, enabling database-based architectural verification. In addition, SQL Server 2005 has greatly extended the functionality of the XML combination, extending the OpenXML () XML decomposition function, and provides a new NODES () function for the XML data type to make lighter The degree of decomposition.
Since the database server enhances this new XML function, it will not be surprising to the SQLClient data provider in Microsoft ADO.NET 2.0. There is also a change to ADO.NET Dataset to support the type XML Datacolumn, and the "integration point" between System.Data and System.xml has been widened. In this article, I will explore the use of SQL Server 2005 XML data types on the client.
SQL Server 2005 can generate two types of XML outputs. Statements Select * from authors for XML Auto generates an XML stream instead of a row of rows. This output type has not changed compared to the output type in SQL Server 2000. Just because of the restrictions in the query analyzer tool, the XML stream output is displayed as a row of rows in the SQL Server query analyzer. You can distinguish this stream from the "Ordinary" column from "XML_F52E2B61-18A1-11D1-B105-000805F49916B" through its specific unique identifier name. This name is actually the underlying TDS (this is a table format data stream, the SQL Server network format) analyzer indicator, in this analyzer, the column should flow to the client, not like ordinary rows. send. There is a special method SQLCOMMAND.EXECUTEXMLREADER to retrieve this special stream on the client. In SQL Server 2005, SELECT ... for XML statements have been enhanced in many ways. There are only several few species here:
1. In most cases, there is a new and easy-to-use for XML Path mode when you need the For XML Explicit mode in SQL Server 2000. 2. Use the Type directive, in addition to generating streams, you can also generate an XML data type column. 3. Can nested for XML expressions. 4. SELECT ... for XML can generate an XML document and an XML fragment using the root instruction. 5. You can pre-hang the standard XSD architecture in advance. You can initially understand the XML is a first-class relational database type. System.data.dbtype and system.data.sqldbType include added value of dbtype.xml and sqldbtype.xml. There is also a new class in the system.data.sqltypes namespace, which is SQLXML. This class acts as an XMLReader instance factory for the XML type value. I will show it through some simple code. Suppose I have a SQL Server table, as shown below:
Create Table XMLTAB
ID Int Identity Primary Key,
XMLCOL XML)
I can use the ADO.NET 2.0 code to access this table on the client.
Using system;
Using system.data;
Using system.data.sqlclient;
Using system.data.sqltypes;
USING SYSTEM.XML;
Void getXmlcolumn {
// "Generic Coding ..." Article for Shows How To
// Get a connection string from a config file
String s = getConnectStringFromConfigfile ("XMLDB");
Using (SqlConnection Conn = New SqlConnection (s))
Using (SQLCommand cmd = new sqlcommand)
"Select * from xmltab", conn))
{
Cn.open ();
SqlDataReader rdr = cmd.executeReader ();
DataTable t = rdr.getschematable ();
While (Rdr.Read ())
{
SQLXML SX = Rdr.getsqlxml (1);
XmlReader XR = SX.CREATEREADER ();
Xr.read ();
Console.writeLine (xr.readouterxml ());
}
}
}
The column data returned when browsing the DATATABLE generated by getSchematable correctly:
ProvIDertype: 25 (25 = XML)
ProviderspecificDataType: system.data.sqltypes.sqlxml
DataType: System.xml.xmlReader
DataTypename:
As is the same as the type built into SQL Server. Note that this column ".NET Type" is XMLReader, for .NET, it is like any XML that is loaded from files or using XMLDocument classes. The XML data type column is as simple as the parameter in the stored procedure or parameterization statement in ADO.NET 2.0:
Using system;
Using system.data; using system.data.sqlclient;
Using system.data.sqltypes;
USING SYSTEM.XML;
Void addarow {
// Get a connection string from a config file
String s = getConnectStringFromConfigfile ("XMLDB");
Using (SqlConnection Conn = New SqlConnection (s))
Using (SQLCommand cmd = new sqlcommand)
"INSERT XMLTAB (XMLCOL) VALUES (@x)", conn))
{
Cn.open ();
Cmd.Parameters.Add ("@ x", sqldbtype.xml);
// Connect the parameter value to a file
XmlReader XR = XmlReader.create ("Somexml.xml");
CMD.Parameters [0] .value = new SQLXML (XR);
INT i = cmd.executenonquery ();
}
}
Back to top
Is it XML or a string?
Both methods in the front code are used in SQL Server specific data types in SQLTYPES. When I use SQLReader's more general accessor method getValue (), the value is significantly different. Columns are not as XMLReader, but as an .NET STRING class. Note that even if the metadata will identify the .NET data type as XmlReader, you cannot convert the column to XmlReader. Returns a string using any accessor except getsqlxml ().
Using system;
Using system.data;
Using system.data.sqlclient;
Using system.data.sqltypes;
USING SYSTEM.XML;
Void getXmlcolumn {
// Get a connection string from a config file
String s = getConnectStringFromConfigfile ("XMLDB");
Using (SqlConnection Conn = New SqlConnection (s))
Using (SQLCommand cmd = new sqlcommand)
"Select * from xmltab", conn))
{
Cn.open ();
SqlDataReader rdr = cmd.executeReader ();
//prints "system.string"
Console.writeline (RDR [1] .gettype ());
// fails, invalid cast
XmlReader XR = (XMLReader) RDR [1];
// this Works
String s = (string) RDR [1];
}
}
Even use the SQLReader.GetProviderspecificValue () method, also returns a string. This is an exception in some extent because getProviderspectFieldType returns system.sql.types.sqlxml. It seems that there is a problem in the current Beta version provider, and this method is not used for the time being.
// system.data.sqltypes.sqlxmlconsole.writeline (rdr.getProviderspecificfieldType (1));
// system.data.sqltypes.sqlstring
Object o = rdr.getProviderspecificValue (1);
Console.writeLine (O.Gettype ());
SQLClient provides a symmetrical feature for the XML parameters, you can also use the String data type. The ability of the intended XML type positional string (nvarchar) is dependent on the fact that SQL Server provides automatic conversion of VARCHAR or NVARCHAR to XML data type. Note that this conversion can also occur in the client, as shown in the following example. Automatic conversion of String / NVARCHAR to XML to store procedures will be valid.
- T-SQL Stored Procedure Definition
Create Procedure Insert_Xml (@X XML)
AS
INSERT XMLTAB (XMLCOL) Value (@X)
// Client-Side Code
Using system;
Using system.data;
Using system.data.sqlclient;
Void insertxmlfromclient {
// Get a connection string from a config file
String s = getConnectStringFromConfigfile ("XMLDB");
Using (SqlConnection Conn = New SqlConnection (s))
Using (SQLCommand Cmd1 = New SQLCommand)
"INSERT XMLTAB (XMLCOL) VALUES (@x)", conn))
Using (SQLCommand Cmd2 = New SqlCommand)
"INSERT_XML", CONN))
{
String str = "
Cn.open ();
// Server-Side Conversion
CMD1.Parameters.Add ("@ x", sqldbtype.nvarchar);
cmd1.parameters [0] .value = STR;
Cmd1.executenonquery ();
// Client-Side Conversion Works TOO
Cmd2.commandtype = commandtype.storedProcedure;
cmd2.parameters.add ("@ x", sqldbtype.xml);
Cmd2.parameters [0] .value = s;
cmd2.executenonquery ();
}
}
Back to top
Document, fragment and for XML support
The XML data type in SQL Server 2005 supports both XML documents and an XML document clip. The difference between the fragment and the document is that the clip can contain multiple top-level elements and empty text nodes. For type XML column / variable / parameters, you can specify whether to allow segments using Document or Content (Allow Fragment) specification. Content is the default value, non-type XML allows segment. The following T-SQL code illustrates the support of the clip:
Create Table Xmltab (ID Int IDentity Primary Key,
XMLCOL XML)
Go
- INSERT A Document
INSERT XMLTAB VALUES ('
- Fragment, Multiple Top-Level Elements
INSERT XMLTAB VALUES ('
- FRAGMENT, BARE TEXT NODE
INSERT XMLTAB VALUES ('Hello World')
- Even thisifment works
INSERT XMLTAB VALUES ('
Select ... for XML also generates an XML fragment. Statements Select Job_ID, Min_LVL, Max_LVL from Jobs for XML AUTO Generates the following output. Please note that there are multiple root elements.
Using SQLXML supports both documents, also supports fragments. The SQLXMLCREATEREADER () method always creates an XMLReader that supports the clip by using the new XmlReadersettings class, as shown below:
// pseudocode from sqlxml.createReader
Street Stm = STM; // Stream Filled from Column (Code Elided)
XmlReadersettings settings = new xmlreadersettings ();
Settings.conformanceLevel = ConformanceLevel.fragment;
XmlReader XR = XmlReader.create
STM, String.empty, Null, NULL, SETTINGS;
If you construct XMLReader in the same way, you can use an XML fragment in the input parameters. Despite the built-in block support when using the SQLXML type, you still need to be careful when processing XmlReader containing a clip. Note that call XmlReader.getouterXML () will only provide the first clip; to locate XMLReader for later segments, you must call the XMLReaderRead method again. I will explain this issue later in this article.
T-SQL "SELECT ... for XML" generates an XML stream instead of a row of rows. It also provides XML with a standard serialization of binary format rather than XML. Due to the different formats, and "Select ... for XML" always generates a clip, use it requires special ways. For this purpose, SQLClient implements a provider specific method SQLCOMMAND.EXECUTEXMLREADER. If you use SQL Server 2000 and ADO 1.0 / 1.1, you need to use ExecuteXmlReader to get the result of the for XML query unless you want to use some of the considerable alternatives that require string connections. With SQL Server 2005 for XML enhancement, you only need to use ExecuteExmlReader to get a single XML stream from SQL Server using ExecuteExmlReader. Since all code written for SQL Server 2000 use this method, this method is also supported in ADO.NET 2.0 and is enhanced. You can use ExecutexmlReader to retrieve any stream from the "for XML" query like in previous versions. In addition, this method supports searching for the XML data type column generated by the normal SELECT statement. The only thing you need to draw here is that when the normal SELECT statement returns multiple rows, ExecuteXmlReader only returns the content of the first line. The example here is displayed using the same table as the previous example:
Using system;
Using system.data;
Using system.data.sqlclient;
Using system.data.sqltypes;
USING SYSTEM.XML;
Void UseexecxmlReader {
// Get a connection string from a config file
String s = getConnectStringFromConfigfile ("XMLDB");
Using (SqlConnection Conn = New SqlConnection (s))
Using (SQLCommand Cmd1 = New SQLCommand)
"Select * from pubs..AUTHORS for XML AUTO, ROOT ('Root')", CONN))
Using (SQLCommand Cmd2 = New SqlCommand)
"Select * from pubs..AUTHORS for XML AUTO", CONN))
Using (SQLCommand cmd3 = new sqlcommand)
"Select * from xmltab", conn))
{
Cn.open ();
// contains document
XmlReader XR1 = cmd1.executexmlreader ();
// Contains Fragment
XMLReader XR2 = cmd2.executexmlreader ();
// Contains Contents of First Row in XMLTAB ONLY
XmlReader XR3 = cmd3.executexmlreader ();
// USE XmlReaders, Then
Xr1.dispose (); xr2.dispose (); xr3.dispose ();
}
}
In order to complete the discussion of XML in ADO.NET 2.0, it is good to mention the life cycle of XMLReader content in various usage scenarios. Studying XmlReader lifecycle also helps understand the buffers of SQLClient execution, and how to use this data to get the highest performance. XMLReader uses resources, in order to release these resources, a Close () or Dispose () method should be called, just like using SqlConnection, SqlCommand and SqldataReader. In the case of reading the XML column through the SqlDataReader, an XMLReader can be assigned to each line. Keep in mind that in order to support the backward movement in the column of the same row, or move to the next line, the content of the XMLReader is buffered in memory. When you use SQLCommand's CommandBehavior.SEquentialAccess, the entire XMLReader will not buffer in memory, but you must be careful when you use this access method. When using CommandBehavior.SEquentialAlaccess, XMLReader associated with columns must be completely consumed before moving to the next column; after moving to the next column, XMLReader seems to be valid, but calling it's READ () method does not generate any data. . When you use ExecuteScalar instead of ExecuteReader, you don't have to understand this behavior, but don't forget to turn off / processes XMLReader. Back to top
Use XML architecture support on the client
SQL Server 2005 supports strong type of XML, which means that XML must meet an XML schema or XML architecture set. This support can be implemented by using architecture sets in SQL Server. The XML architecture set is as defined as any other SQL Server object, and the XML schema is stored in SQL Server. The Usage of the T-SQL DDL CREATE statement and the XML architecture collection is as follows:
Create XML Schema Collection Books_XSD
AS
- One or More XML Schemas Here
Go
Create Table Typed_xml
ID Int Identity Primary Key,
- Require Books_col content to be schema-valid
Books_col xml (books_xsd)
)
- Validated Here
INSERT TYPED_XML VALUES ('')
- Validated Here Too
Update typed_xml
Set books_col.modify ('')
WHERE ID = 1
When you use SQL Server 2005 from the client, verification is performed on the server instead of executing on the client. For example, if you add a row to the TydAd_xml table using the Addarow method displayed in the previous example, the data is sent to SQL Server before the verification occurs. However, only a small amount of work can be retrieved from SQL Server XML Schema Collectes and hide them on the client to complete the client's verification. This prevents the user or web service from sending the architecture invalid XML to the client used for SQL Server, saving some repeated operations, but must consider two warnings / clarifications. First, depending on the XML architecture information acquired from SQL Server, the client metadata is dependent on any cache. Maybe someone has changed the architecture collection using the T-SQL ALTER XML Schema statement, or even delete the schema collection and recreate it, so that your client's check is useless. You can use new Event Notification on the Create / ALTER / DROP XML Schema DDL statement to prevent exceptions. You will then use a custom code to monitor Service Broker Service, which is similar to SQLNotificationRequest with query notifications. Event Notifications is a new feature in SQL Server 2005, but it is similar to the query notification discussed in the query notification in the previous article ADO.NET 2.0. Second, please remember, even if you perform XML architectures on the client, SQL Server will also retrieve on the server. There is no way to indicate SQL Server "I know that this SQL Server architecture type XML instance is valid, please don't check it yourself." To perform the client's XML architecture verification, you can use the T-SQL function XML_SChema_NameSpace () to retrieve SQL Server XML Schema Collection. This requires an XML architecture set name and database architecture name because the XML architecture set includes a database architecture. We can hard encode them in the program, or extract them from the client line collected metadata data. It is easy to get the name of the architecture set associated with a particular column by using the SqlDataReadergetsChematable method or using the new SQLMetadATA class. Here is a small code example:
Using system;
Using system.data;
Using system.data.sqlclient;
Using system.data.sql;
Void getCollectionInfo {
// Get a connection string from a config file
String s = getConnectStringFromConfigfile ("XMLDB");
Using (SqlConnection Conn = New SqlConnection (s))
Using (SQLCommand cmd = new sqlcommand)
"SELECT BOOKS_COL from Typed_xml", Conn))
{
Cn.open ();
// Fetch Only SQL Server Metadata
SqlDataReader Rdr = cmd.executeReader (Commandbehavior.Schema);
SQLMetadata MD = rdr.getsqlmetadata (0); string database = md.xmlschemacollectionDatabase;
String Schema = md.xmlschemacolectionowningschema;
String collection = md.xmlschemacolectionname;
}
}
Once you understand which XML schema collection to retrieve, you can use the T-SQL function to retrieve it into the client XMLschemaset. Note that this example also shows how to use the XMLReader retrieved clip. This is required because the XML architecture in XML Schema Collection may not only; the XML_SCHEMA_NAMESPACE function returns all the XML schema in the collection as a clip.
Using system;
Using system.data;
Using system.data.sqlclient;
Using system.data.sqltypes;
USING SYSTEM.XML;
USING SYSTEM.XML.SCHEMA;
Void getschemaset {
// Get a connection string from a config file
String s = getConnectStringFromConfigfile ("XMLDB");
Using (SqlConnection Conn = New SqlConnection (s))
Using (SQLCommand cmd = new sqlcommand)
"SELECT XML_SCHEMA_NAMESPACE (N'dbo ', N'Books_xsd')", CONN)
{
Xmlschemaset ss = new xmlschemaset ();
Cn.open ();
SqlDataReader rdr = cmd.executeReader ();
RDR.READ ();
XmlReader XR = rdr.getsqlxml (0) .createReader ();
DO
{
SS.ADD (XMLschema.read (XR, NULL);
Xr.read ();
}
While (xr.nodetype == xmlnodetype.element);
}
}
If there is xmlschemaset in your hand, we can integrate the client XML authentication code into the Add routine. You are! This is the client verification.
Void ValidateAndStore (Xmlschemaset SS)
{
// Associate the xmlschemaset with the xmlreader
XmlReadersettings settings = new xmlreadersettings ();
Settings.schemas = ss;
String s = getConnectStringFromConfigfile ("XMLDB");
Using (XmlReader XR = XmlReader.create)
"file: // c: /temp/somefile.xml", settings))
// Get a connection string from a config file
Using (SqlConnection Conn = New SqlConnection (s))
Using (SQLCommand cmd = new sqlcommand)
"INSERT TYPED_XML VALUES (@X)", conn) {
Try
{
Cn.open ();
// Should Throw an Exception Here if Not Schema Valid
Cmd.Parameters.addwithValue ("@ x", new sqlxml (xr));
INT i = cmd.executenonquery ();
}
Catch (Exception E)
{
Console.writeLine (E.MESSAGE);
}
}
}
Although not every application requires client XML architecture verification, it is very exciting when the application needs it. When you use the new SQL Server 2005 XMLschema option on the Select ... for XML statement, an XML schema is also generated:
Select * from authors for XML AUTO, Elements, Xmlschema
You can also end the fragment and retrieve XMLschemaset for these types of queries on the client, although there are still some limitations that actually perform such an operation in the current beta.
Back to top
summary
I have described how to use new XML types and new For XML features in Microsoft ADO.NET SQLClient using XML from Microsoft SQL Server, and how to insert XML into the SQL Server table using SQLClient. In fact, there are still some enhanced XML features in DataSet I have not mentioned that there will be a series of articles about Ado 2.0 DataSet enhancements. You can read more information about using SQL Server XML data in MSDN online article XML Support in Microsoft SQL Server 2005, the author is Shankar Pal, Mark Fussell and Irwin Dolobowsky; in MSDN Online Article What's New in for XML in Microsoft SQL Read more information about Select ... for XML in Server 2005, the author is Michael Rys; and reads in Mark Fussell's What's New in System.xml for Visual Studio 2005 and The .NET Framework 2.0 Release More information about SYSTEM.XML in .NET 2.0. This statement on XML and ADO.NET 2.0 and SQLClient has been integrated at many levels; the integration of data models is more strict than ever.
About author
Bob Beauchemin is a faculty member of the Developmentor, a courses author and a database program contact. He has more than twenty-five years of designers, programmers and administrators in data-centric distributed systems. He wrote an article about ADO.NET, OLE DB and SQL Serverfor Microsoft Systems Journal, SQL ServerMagazine, and other content, and is the author of the A First Look at SQL Server 2005For Developers and Essential ADO.NET.
Go to the original English page
