XML Microsoft SQL Server 2005 is the best implementation strategy Date: 7/5/2004 | Updated: 7/5/2004 Author: Shankar Pal, Vishesh Parikh, Vasili Zolotov, Leo Giakoumakis, Michael Rys
Microsoft Corporation
Applicable to:
Microsoft SQL Server
Summary: Learn about XML data modeling and use criteria in SQL Server 2005, and observe some illustrative examples.
This page Context Data Modeling Using XML Data Types Data Modeling Usage Cross - domain Query For Native XML Supported Directory View
Introduction Microsoft SQL Server 2005 provides extensive support for XML data processing. The XML value can be naturally stored in the XML data type column, and the latter can be typed according to the set of XML architectures, or maintain non-type. You can encode an XML column into an index. Moreover, using XQuery and XML DML (extended to data modifications) can support fine-grained data operations.
SQL Server 2000 and SQLXML Web Release provide powerful XML data management features. These features are dedicated to the mapping between relational data and XML data. You can use the XSD (AXSD) with annotations to define the relational data to provide an XML-centric approach that supports the bulk data load, query and update features of XML data. Transact-SQL extensions provide SQL-centric approach to map relationship query results to XML (using for XML), and generate a relationship view from XML (using OpenXML). These support have been extended in SQL Server 2005. Combined with new native XML support, SQL Server 2005 provides a powerful platform for semi-structural and unstructured data management to develop features rich applications.
This article provides XML data modeling and use guidelines in SQL Server 2005. It contains two two topics:
? Data modeling
XML data can be stored in a variety of ways in SQL Server 2005, for example, using the native XML data type and XML dispersed into the table. This topic provides a criterion for making appropriate options to model XML data. At the same time, it is also discussed to encrypt the XML data into index, attribute promotion, and the type of XML instance. Usage
This topic discusses the topics related to usage (if XML data is loaded into the server and the type reasoning in query compilation), interpret and distinguish closely related functions, and recommend appropriate use of these functions. Various concepts were elaborated in this paper.
In order to maximize the content of this article, you should have a basic understanding of the XML feature in the SQL Server environment. See XML Support in Microsoft SQL Server 2005.
Back to Top Data Modeling This section outlines the reason for using XML in SQL Server 2005, provides guidelines for selecting between native XML storage and XML view technology, and provides data modeling suggestions.
Relationship or XML Data Model If your data is highly structured, a known architecture, the relationship model may be most effective for data storage. Microsoft SQL Server provides the necessary features and tools you may need. On the other hand, if the structure is flexible (semi-structural and unstructured) or unknown, it must be appropriately considered to model such data.
If you need to be independent of the platform's model to ensure portability of data using structured and semantic tags, XML is a nice choice. Moreover, if you meet some of the following properties, it is still a proper selection: • Your data is more sparse, or you don't understand the structure of the data, or the structure of the data may have major changes in the future. • Your data represents the container hierarchy (relative to references in the entity) and may be recursive. • Your data has an inherent order. • You want to query the data or update some data based on its structure.
If all of the above conditions are not met, you should use the relational data model. For example, if your data is an XML format, but your application rarely uses the database to store and retrieve data, the [n] varchar (max) column can meet all of your needs. Storage data in the XML column can bring other benefits - the engine will check the data format specification or valid, and support queries and updates to XML data.
Reasons for storing XML data in SQL Server 2005 The reason why some of the native XML features in SQL Server 2005 is used instead of managing XML data in a file system:
• You want to use the management function of the database server to manage XML data (for example, backup, recovery, and replication). • You want to share, query, and modify XML data with efficient ways and transaction processing methods. Fine-grained data access is important for your application. For example, you may need to extract some of the section inside the XML document, or you may need to insert a new section instead of replacing the entire document. • You have relationship data and SQL applications that you want to interoperate between relations data and XML data within the application. For cross-domain applications, you need language support for queries and data modifications. • You want the server to ensure data format specification and can verify data depending on the XML architecture. • You need to program an XML data into an index to achieve efficient query processing and good scalability, and use a first-class query optimizer. • You want to access the XML data to SOAP, ADO.NET, and OLE DB.
If any of the above conditions is not met, you'd better store data as a large data type of non-XML, such as [n] varchar (max) or varbinary (max).
The XML Storage Options SQL Server 2005's storage options are as follows:
• This unit stores uses an XML data type:
Data is stored in an internal representation of XML content (such as container hierarchism, document order, element, and attribute value, etc.). Specifically, it is to retain the information set of XML data (for more information on the information set, see http://www.w3.org/tr/xml-infoseet). It may not be a precise copy of text XML, because the following information is not retained: irrelevant space, attribute order, named space prefix, and XML declaration.
For types of XML data types (ie, XML data type bound to XML architecture), responsible for encoding the post schema validation infoset, PSVI) to the information set to add type information. This will significantly improve the analysis speed. (For more information, see the W3C XML architecture specification, URL http://www.w3.org/tr/xmlschema-1 and http://www.w3.org/tr/xmlschema-2.) XML and Mapping between relational storage:
With an annotation (AXSD), XML will be broken down into columns in one or more tables, and reserved the fidelity-reserved hierarchy in relationship level, but ignore element order. The architecture cannot be recursive. Large object storage ([N] VARCHAR (MAX), and VARBINARY (MAX): Store an exact copy of the data. This is useful for special applications (such as legal documents). Most applications do not require precise copies, XML content (information intensity) can meet the needs.
Normally, it may be necessary to use these methods. For example, you may need to store XML data with an XML data type column and increase the properties into the relationship column. Instead, you might want to use mapping techniques to store non-recursive parts into non-XML columns, but only the recursive part is stored in the XML data type column.
The selection of XML technology (native XML and XML view) is usually depends on the following factors:
• Storage options:
Your XML data may be more suitable for large object storage (eg, product manuals), or more suitable for storage in relationships (eg, transition to XML rows). Each storage option retains document fidelity to varying degrees. ? Query function:
Based on the nature of the query and the extent to which XML data are queried, you may find that a storage option is more suitable than other storage options. The fine-grained XML data query (for example, predicate calculations on the XML node) are attached to varying degrees in these two storage options. • Cut the XML data into the index:
You may want to encrypt the XML data in order to improve XML query performance. Index options are different from the storage options; you need to make appropriate options to optimize workload. • Data modification features:
Some workloads involve modifications for fine-grained to XML data (for example, add new sections within document), while other workloads do not involve (eg, web content). For your application, data modification language support may be important. ? Architecture support:
Your XML data may be described by architecture, which may not be an XML architecture document. The support of the architecture binding XML depends on XML technology.
Needless to say, different choices have different performance characteristics.
The native XML store can store your XML data in the XML data type column of the server. In the following cases, this will be an appropriate choice:
• You need a simple way to store XML data on the server, while reserving document order and document structure. • Your XML data may have no architecture. • You need to query and modify your XML data. • You need to encrypt the XML data into the index to achieve a more fast query process. • Your application requires a system directory view to manage your XML data and an XML architecture.
When your XML document has a variety of structures, or when your XML documents meet different or complex architectures, these architectures are difficult to map the relational structure, the native XML storage will be useful.
Example: Modeling XML data using an XML data type considers an XML format product manual, each of which corresponds to a separate chapter, and there are multiple sections in each chapter. The section can contain multiple subsections, so it is a recursive element. The product manual contains a lot of mixed content, charts, and technical information; data is semi-structural. Users may wish to perform search on the topic of interest (for example, in the "index" section of the "aggregation index", and query the number of techniques. The appropriate storage model of the XML document is the XML data type column. This can preserve the information set of XML data. Adding an XML column to an index to improve query performance.
Example: Accurate copy of the XML data assumes that the Government Act requires you to retain an exact text copy of an XML document (for example, the signed document, legal document or stock trading order). You may need to store your document in the [N] VARCHAR (MAX) column.
For queries, the data can be converted to an XML data type at runtime and then perform XQuery. Runtime conversion may cost high, especially when the document is large. If you often query, you can use a redundancy to store the document in the XML Data Type column and you can retrieve the index while returning a precise document replica from the [N] Varchar (Max) column.
The XML column may be based on the [N] varchar (max) column calculation column. You cannot create an XML index on the XML computing column, or you cannot generate an XML index on [N] VARCHAR (MAX) or Varbinary (Max) column.
XML View Techniques You can create a "XML view" of persistent data by defining a mapping between the XML architecture and the database. You can use an XML bulk load to populate the base table using an XML view. You can query the XML view using XPath 1.0; the query will be converted to the SQL query for the table. Similar to this, the update will also be passed to these tables.
This technology is useful in the following cases:
• You want to have an XML-centric programming model that uses an XML view on existing relational data. • Your XML data has architecture (XSD, XDR), which may be provided by external partners. • The order of data is not important, or your query data is not recursive, or you have known the maximum recursive depth. • You want to query and modify data by using XML views of XPath 1.0. • You want to load XML data in batches and break down to the base table using the XML view.
Examples of this include an XML data having a data exchange and a Web service in an XML form, as well as XML data having a fixed architecture. For more information, see the SQLXML Developer Center.
Example: Modeling data using an annotated XML schema (AXSD) assumes that you have some relationship data that wants to operate in XML (for example, customers, orders, and row items). Use AXSD to define an XML view on the relational data. With an XML view, XML data can be loaded into the table and query and update the relational data using XML view. This mode is useful if you need to switch data with XML tag in other applications while your SQL application is working.
Mixed models are sometimes suitable for combining relational data and XML data type columns. Some values in XML data can be stored in the relationship and store the remaining or all XML values in the XML column. This may produce better performance (for example, you can fully control the index created on the relationship) and the lock characteristics. However, this requires you to take more responsibility to manage data storage.
The value to be stored in the relationship depends on your workload. For example, if you retrieve all XML values based on the path expression / Customer / @ Custid, you may generate higher query performance by lifting the value of the CustID property to the relationship and the index is enabled. On the other hand, if your XML data is widely decomposed into a relationship, the re-combined cost may be large. For highly structured XML data (eg, the contents of the table have been converted to XML), all values can be mapped to the relationship column (possibly using XML view technology).
Back to top A data modeling topic for native XML storage is discussed using an XML data type. These topics include encrypting XML data into index, attribute promotion, and type XML data types.
The same or different table XML data type columns can be created in a table containing other relationships, or can be created in a separate table with foreign key relationships with the primary table.
When you meet a certain condition, create an XML data type column in the same table:
• Your application performs data retrieval on the XML column and does not require an XML index on the XML column. Or? You need to generate an XML index on the XML data type column, and the primary key of the primary table is the same as its aggregation keys. For more information, see Cushing an XML Data Type Column in a section.
When meeting the following conditions, create an XML data type column in a separate table:
• You need to generate an XML index on the XML data type column, but the primary key of the primary table is different, or the primary table does not have a primary key, or the primary table is a heap (that is, there is no aggregation button). This may be true if the primary table already exists. • You don't want the table to scan a reduction in the speed due to the XML column exists in the table (whether it is stored in the row or stored outside the table.
The granularity of XML data stored in the granular XML column of XML data is critical to lock and update features. SQL Server uses the same locking mechanism for XML and non-XML data. Therefore, row-level locking results in all XML instances in the respective rows being locked. When the particle size is relatively large, the large XML instance is locked to perform updates, which can cause a decline in throughput under multi-user. On the other hand, severe decomposition will lose object package and improve re-combined costs.
Updating an XML instance replaces an existing instance to an updated instance, even if only the value of a single attribute is modified. The larger the xml data particle size, the higher the update cost. Smaller XML instances produce higher update performance.
For good designs, it is important to maintain the balance between data modeling needs to be related to the locking and update features.
Non-type, type, and constrained XML data type SQL Server 2005 XML data type implements the ISO SQL-2003 standard XML data type. Therefore, it can store the format specification XML 1.0 document in the non-type XML column and so-called XML content segments with text nodes and any number of top-level elements. The system will check if the format of the data is specified, and the column is not required to bind the column to the XML architecture, and the data that is not specified in the extension sense is rejected. This is also true for non-type XML variables and parameters.
If you have an XML schema that describes XML data, these architectures can be associated with the XML column to generate type XML. The XML schema is used to validate the data, perform more accurate type checks than non-type XML during compilation, and optimize storage and query processing during compilation of query and data modification statements.
Under the following conditions, use a non-calorization XML data type:
• You have no architecture corresponding to XML data? You have an architecture, but you don't want the server to validate the data. When the application performs client authentication before using the data in the server, or temporarily store XML data ineffective according to the architecture, or when using the unsupported architecture component (for example, Key / Keyref) in the server, this sometimes occurs Happening. Under the following conditions, use the type of XML data type:
• You have an architecture corresponding to XML data, and I hope that the server validates XML data according to the XML architecture. • You want to make full use of storage and query optimization based on type information. • You want to use type information more fully in the compilation process of the query.
Type XML columns, parameters, and variables can store XML documents or content - you must specify them as flags when declaring (specified as Document or Content, respectively). Moreover, you must provide an XML architecture collection. If each XML instance is exactly a top element, specify documents; otherwise, use Content. The query compiler uses the Document tag in the type check of the compilation process to reason in the unique top element.
In addition to the XML column type, you can use a relationship (column or row) constraint on the type of type or non-type XML data type. Under the following conditions, please use the constraint:
• The business rules cannot be represented in the XML architecture. For example, the delivery address of the flower shop must be within 50 miles around its business place, which can be written as constraints on the XML column. This constraint may involve an XML data type method. • Your constraint involves other XML columns or non-XML columns in the table. An example of this is to enforce the Customer ID (/ Customer / @ Custid) in the XML instance to match the value in the relational CustomerID column.
Document Type Definition (DTD) XML Data Type Column, variables, and parameters can be used using an XML architecture rather than DTD. However, for non-type and type XML, the inline DTD can be used to provide default values to replace the entity reference to their extended form.
You can use a third-party tool to convert DTD to an XML architecture document and load an XML schema into a database.
Create an XML Data Type Column to create an XML index on the XML data type column. This will enable indexing of all tags, values, and paths on the XML instance in this column to improve query performance. Under the following conditions, your app may benefit from an XML index:
• Query the XML column is common in your workload. The XML index maintenance costs during the data modification must be considered. The XML value is relatively large, and the searched part is relatively small. Generating indexes can avoid all data at runtime and increase the performance of query processing because it benefits from index lookups.
The first index on the XML column is "primary XML index". Through this index, three types of auxiliary XML index can be created on the XML column, thereby increasing the speed of queries of common types, as described below.
The primary XML index is to program all tags, values, and paths within the XML instance in the XML column to indexes. The base table (i.e., a table containing the XML column) must have a aggregation index on the primary key of the table; the primary key is used to associate the index line with the row in the base table. Retrieve intact XML instances (such as Select *) from the XML column. The query uses the primary XML index and returns the scalar value or the XML subtree using the index itself.
Example: Creating a primary XML Index In our most examples, use Table T (PK Int Primary Key, XCOL XML) with non-type XML columns, which can be simply expanded into the form of type XML (related For information on the type of XML, see SQL Server 2005 online books). For convenience of explanation, the query will be described for the XML data example as follows: Writing Secure code </ title> <author> <first-name> michael </ first-name> <last-name> howard </ last-name> </ author> <author> <first-name> David </ first-name> <last-name > Leblanc </ last-name> </ author> <price> 39.99 </ price> </ book></p>
<p>The following statement creates an XML index called IDX_xcol on the XML column Xcol of Table T:</p>
<p>Create Primary XML INDEX IDX_XCOL ON T (XCOL)</p>
<p>Auxiliary XML Index After creating the primary XML index, you may want to create a secondary XML index to increase the speed of different kinds of queries in the workload. Three types of auxiliary XML indexes - Path, Property, and Value provide assistance for path-based queries, custom attribute management, and value-based queries. The PATH index is on all XML instances in the column, and the B tree of the Path, Value is generated in the document order. Property index Creates a gathering B tree in each XML instance (PK, PATH, VALUE), where PK is the primary key of the base table. Finally, the value index in all XML instances in the XML column, create the B tree of Value, Path) pairs in the order of documentation.</p>
<p>Here are some guidelines for creating one or more indexes described above:</p>
<p>• If the workload uses a large number of path expressions in the XML column, the PATH auxiliary XML index may speed up the processing speed of the workload. The most common example is an exist () method for the XML column in the WHERE clause of T-SQL. • If your workload retrieves multiple values from a separate XML instance in the XML instance, it may be useful in the path to the path in each XML instance to the Property index. This situation usually appears in the attribute packet, at which point the properties of the object are acquired and its main key value is known. • If your workload involves querying the value in the XML instance, you may need to create a value index. This typically occurs in a sub-circular view, such as // author [Last-name = "Howard"], where the element can appear on any level of the hierarchy. This situation also occurs in the "wildcard" query, such as / book [@ * = "novel"], where the query will look for an element with a certain value "Novel". Example: Path-based lookup hypothesis The following query is very common in your workload:</p>
<p>SELECT PK, XColfrom Twhere Xcol.exist ('/ book [@genre = "novel"]) = 1</p>
<p>Path Expressions / Book / @ Genre and Value "Novel" corresponds to the key field of the PATH index. Therefore, the auxiliary XML index of the PATH type is useful for this workload:</p>
<p>CREATE XML INDEX IDX_XCOL_PATH ON T (XCOL) Using XML INDEX IDX_XCOL for PATH</p>
<p>Example: Gets the properties of the object, consider the following query, which retrieves the properties of a book from the various rows of Table T, "Title", and ISBN:</p>
<p>Select Xcol.value ('(/ Book / @ Genre) [1],' VARCHAR (50) '), Xcol.Value (' (/ Book / Title) [1], 'Varchar (50)'), Xcol.value ('(/ book / @ ISBN) [1]', 'VARCHAR (50)') from T</p>
<p>In this case, the attribute index is useful, and its creation is as follows:</p>
<p>CREATE XML INDEX IDX_XCOL_PROPERTY ON T (XCOL) Using XML INDEX IDX_XCOL for Property</p>
<p>Example: Value-based query is specified in the following query, the sub-generation axis or the own shaft (//) specifies the partial path so that the ISBN value can benefit from the Value index:</p>
<p>Select Xcolfrom Twhere Xcol.exist ('// Book [@isbn = "1-8610-0157-6"]) = 1</p>
<p>The Value index is created as follows:</p>
<p>CREATE XML INDEX IDX_XCOL_VALUE ON T (XCOL) Using XML INDEX IDX_XCOL for VALUE</p>
<p>Full-text index on the XML column You can create a full-text index on the XML column to encrypt the contents of the XML value into the index, and ignore the XML tag. The attribute value is not included in the full-text index (because they are considered part of the tag), and the elemental tag is used as the marker boundary. In some cases, the full-text search can be combined with the XML index method:? First, use the SQL full-text search to filter the XML value of interest. Next, query these XML values, which uses the XML index on the XML column.</p>
<p>Example: Combine full-text search with XML query After creating a full-text index on the XML column, the following query checks if the XML value contains words "Custom" in the book name:</p>
<p>Select * from t where contacts (Xcol, 'Custom') and xcol.exist ('/ book / title / text () [Contains (., "Custom")]') = 1</p>
<p>The Contains () method uses a full-text index to combine the XML value of the text "Custom" in any part of the document as a subset. The exist () clause ensures that the word "Custom" appears in the book name.</p>
<p>Full-text search using Contains () and XQuery Contains has different semantics. The latter is a sub-string matching, and the former is the same as the word derived tag. Therefore, if you want to search the string "Run" in the title, "Run", "Runs", "Running" will match because full-text contacts () and xQuery contacts are satisfied. However, the above query does not match the word "customization" in the title. (Full text "failed, and XQuery Contains () is satisfied). Typically, for pure sub-string matching, the full-text Contains () clause should be deleted.</p>
<p>Moreover, full-text search is derived from words, and XQuery Contains () is a literal match. This difference will be elaborated in the next example.</p>
<p>Example: Use the word derived to the XML value for full-text search Normally, the example cannot be excluded: combine the full-text search with the XML query to check. Consider query:</p>
<p>Select * from T where Contains (Xcol, 'Run')</p>
<p>Because words derived using words, the word "RAN" in the document matches the search criteria. Moreover, you do not check the search context when using XQuery.</p>
<p>When using AXSDs using the full-text index to decompose XML to the relationship column, the XPath query on the XML view will not perform full-text search for the base table.</p>
<p>Attribute enhancement If a small amount of element and attribute values are mainly queried (for example, the customer ID lookup customer, that is, the value specified / customer / @ custid), you may want to enhance these quantities to the relationship column. This will be useful when the entire XML instance is retrieved, but only when a small part of XML data is queried. Creating an XML index on the XML column is not necessary; in contrast, you can encode an index. The query must be written to use the enhanced column (ie, the query optimizer does not redirect the query of the XML column to the raise column).</p>
<p>The raised column can be the computational column in the same table, or a column that is separate and user-maintained in the table. This is enough when upgrading the unique value (ie, single-value properties) from each XML instance. However, for multi-value properties, you must create a separate table for this property, as described below. Calculation columns based on XML data types can create computational columns using user-defined functions (UDF) capable of activating an XML data type method. The type of column can be any SQL type, including XML. The following example illustrates this.</p>
<p>Example: Based on the XML Data Type Method Calculating ISBN Create User-Defined Functions:</p>
<p>Create Function UDF_GET_BOOK_ISBN (@XData XML) Returns Varchar (20) Begin Declare @isbn varchar (20) SELECT @isbn = @ xdata.value ('/ book [1] / @ ISBN', 'VARCHAR (20)') Return @ ISBN END</p>
<p>Add a compute column to the table:</p>
<p>Alter Table Tadd Isbn as dbo.udf_get_book_isbn (Xcol)</p>
<p>Calculatory columns can be used in the usual manner.</p>
<p>Example: The query on the calculation column of the XML data type method is to get its ISBN to 0-7356-1588-2, you can rewrite the query on the XML column</p>
<p>Select Xcolfrom Twhere Xcol.exist ('/ Book [@isbn = "0-7356-1588-2"]) = 1</p>
<p>To use the calculation column, as shown below:</p>
<p>Select Xcolfrom Twhere ISBN = '0-7356-1588-2'</p>
<p>You can create a user-defined function that returns the XML data type and the use of the UDF. However, the XML index cannot be created on the calculated XML column.</p>
<p>Creating Properties Tables You may want to boost certain multi-value properties in XML data to one or more tables, create an index on these tables, and redirect queries to use these tables. Typical situations are a small part of the property covers most of the query workload. You can do the following:</p>
<p>• Create one or more tables to store multi-value properties. You may find that it is convenient to use the following processing: each table stores an attribute and copies the primary key of the base table in the attribute table to connect to the base table. • If you want to maintain the relative order of properties, you need to introduce a separate column for the relative order. • Create a trigger on the XML column to maintain the properties table. In the trigger, do the following: • Insert and delete rows using an XML data type method (such as nodes () and value ()) in the property table. (For more information on the Nodes () method, see Value (), NODES () and OpenXML ().).) • Create a stream meter value function in the CLR to insert and delete rows in the properties table. Written inquiry so that the properties table is SQL access, and XML access to the XML column in the base table, which requires the primary key of these tables to join each other.</p>
<p>Example: Creating a Property Table To assume you want to enhance the author's name. Books have one or more authors, so the name is a multi-value attribute. Each name is stored in a single row of the property sheet. The primary key of the base table is copied in the attribute table to join the rear.</p>
<p>Create Table TBLPropauthor (Proppk Int, Proppk Int, PropAuthor Varchar (MAX)</p>
<p>Example: Create a user-defined function to generate a list value function UDF_XML2Table under the XML instance to accept a primary key value and an XML instance. It will retrieve all the names of all authors and return (primary keys, name) to the routine. create function udf_XML2Table (@pk int, @xCol xml) returns @ret_Table table (propPK int, propAuthor varchar (max)) with schemabindingasbegin insert into @ret_Table select @pk, nref.value ( '.', 'varchar (max)' ) From @ Xcol.nodes ('/ book / author / first-name') R (NREF) Returnend</p>
<p>Example: Create a trigger to insert a trigger in a filling profile: Insert a row in the attribute table</p>
<p>Create Trigger TRG_DOCS_INS ON T for Inserts Declare @WANTEDXML XML Declare @fk tentSelect @WANTEDXML = Xcol from Inserted Select @fk = pk from inserted</p>
<p>INSERT INTO TBLPROPAUTHORSETOT * from DBo.udf_xml2table (@fk, @WANTEDXML)</p>
<p>Delete trigger: Delete rows from the property table based on the primary key value of the delete row</p>
<p>Create Trigger TRG_DOCS_DEL ON T for Deleteas Declare @fk int search @fk = pk from deleted delete TBLPROPAUTHOR WHERE PROPPK = @fk</p>
<p>Update Trigger: Remove the existing row in the property table corresponding to the updated XML instance, and insert a new line in this attribute table</p>
<p>Create Trigger TRG_DOCS_UPDON TFOR Updateasif Update (Xcol) Or Update (PK) Begin Declare @fk Int Declare @WANTEDXML XML SELECT @fk = pk from deleted delete TBLPROPAUTHOR WHERE PROPPK = @fk</p>
<p>SELECT @WANTEDXML = Xcol from insertedselect @fk = pk from inserted</p>
<p>INSERT INTO TBLPROPAUTHOR SELECT * from DBO.UDF_XML2TABLE (@fk, @WANTEDXML) END</p>
<p>Example: Looking for an XML instance of the author's name to "David" can indicate the query on the XML column. In addition, you can search the name "DAVID" in the attribute table, and then perform the backward connection with the base table to return an XML instance, as shown below:</p>
<p>Select Xcol from T Join TBLPROPAUTHOR ON T.PK = TBLPROPAUTHOR.PROPPKWHERE TBLPROPAUTHOR.PROPAUTHOR = 'DAVID'</p>
<p>Example: Solution Using CLR Flow Table Value Functions This solution includes the following steps: (a) Defining a CLR class SQLReaderBase, it implements IsqlReader, and generates a stream table value output by applying path expression on an XML instance; (b) Creating an assembly and a T-SQL user-defined function (UDF) to activate the CLR class; (c) Using the UDF definition insert, update, and delete triggers to maintain the properties table. First, create a streaming CLR function, which is as follows. The XML data type is disclosed for managed class SQLXML in ADO.NET; it supports returns to XMLReader CreateReader ().</p>
<p>Public class c_streaming_xml_tvf {public static isqlreader streaming_XML_TVF (SQLXML XMLDOC, String PatHexpression) {Return (New TestsqlreaderBase (XmlDoc, PatHexpression);}}</p>
<p>// Class that implements ISqlReaderpublic class TestSqlReaderBase: ISqlReader {XPathNodeIterator m_iterator; ??? public SqlChars FirstName; // Metadata for current resultsetprivate SqlMetaData [] m_rgSqlMetaData;</p>
<p>Public TestsqlreaderBase (SQLXML XMLDoc, String PatHexpression) {// variables for xpath navigation xpathdocument xdoc; xpathnavigator xnav; xpatHexpression Xpath;</p>
<p>// set SQL meta data m_rgsqlmetaData = new sqlmetadata [1]; m_rgsqlmetata [0] = new SQLMetadata ("firstname", sqldbtype.nvarchar, 50);</p>
<p>// Set up the Navigator if xDoc = new XPathDocument (xmlDoc.CreateReader ()) (xmlDoc.IsNull!); Else xDoc = new XPathDocument (); xNav = xDoc.CreateNavigator (); xPath = xNav.Compile (pathExpression); m_iterator = xnav.select (xpath);} public bool read () {bool morewes = true; if (morelows = m_iterator.movenext ()) ??? firstname = new sqlchars (m_iterator.current.value); returnirerows;} }</p>
<p>Next, create an assembly, and a T-SQL user-defined function SQL_STREAMING_XML_TVF corresponding to the CLR function streaming_xml_tvf (not shown). This UDF is used to define a table value function CLR_UDF_XML2TABLE to generate a routine:</p>
<p>create function CLR_udf_XML2Table (@pk int, @xCol xml) returns @ret_Table table (FK int, FirstName varchar (max)) with schemabindingasbegin insert into @ret_Table select @pk, FirstName FROM SQL_streaming_xml_tvf (@xCol, '/ book / author / first -name ') Returnend finally defines the trigger, such as the example creates a trigger to fill the property table, but replace UDF_XML2TABLE with the function clr_udf_xml2table. Therefore, the insertion trigger will be as follows:</p>
<p>Create Trigger CLR_TRG_DOCS_INS ON T for INSERTASDECLARE @WANTEDXML Xmldeclare @fk Intselect @WANTEDXML = Xcol from insertedselect @fk = pk from inserted</p>
<p>INSERT INTO TBLPROPAUTHOR SELECT * from DBo.clR_udf_xml2table (@fk, @WANTEDXML)</p>
<p>The delete trigger is identical to the non-CLR version, while the update trigger only replaces the function udf_xml2table () to CLR_UDF_XML2TABLE ().</p>
<p>The XML architecture set XML architecture set is a metadata entity that determines by the relational architecture, including one or more possible related (eg, pass) or unrelated XML architectures. A separate XML architecture in the XML architecture collection is identified by its target namespace.</p>
<p>The XML architecture collection is created using Create XML Schema Collection syntax and provides one or more XML architectures. More XML schema components can be added to the existing XML architecture, and you can add more architectures to the XML schema collection using the Alter XML Schema Collection syntax. You can use the security model in SQL Server 2005 to ensure the security of the XML architecture collection as any SQL object.</p>
<p>Multi-Type Columns XML Architecture Collection C Type XML column Xcol by multiple XML architecture. In addition, flag Document or Content specifies whether an XML tree or fragment can be stored in column Xcol.</p>
<p>For Document, each XML instance will be used to verify and type namespaces for verification and type, specify the target namespace of the top elements in the instance. On the other hand, for Content, each top level element can specify any of the target namespaces in C. The XML instance will verify and type all the target namespaces present in the instance.</p>
<p>Architecture Evolution XML architecture set is used to type XML columns, variables, and parameters. It provides an XML architecture evolution mechanism. Suppose you add an XML schema with the target namespace Book-V1 to the XML architecture set C. XML column XCOL using C can store XML data that complies with the BOOK-V1 architecture.</p>
<p>Suppose an application wants to extend the XML architecture through the new architecture component (such as complex type definitions, and top-level element declarations). These new architecture components can be added to the Book-V1 architecture and do not require re-authentication of existing XML data in column XCOL.</p>
<p>Suppose the app then wishes to provide a new version of the XML architecture and select the target namespace Book-V2 for the new version. The XML architecture can be added to C. The XML column can store the two instances of Book-V1 and Book-V2, and perform queries and data modifications to XML instances that match these namespaces. Back to top Upload XML Data Put XML Data From SQL Server 2000 to SQL Server 2005 You can transfer XML data to SQL Server 2005 in a variety of ways. In the next section, we will discuss several solutions.</p>
<p>• If you store data in the [N] Text or Image Column of the SQL Server 2000 database, you can import tables into the SQL Server 2005 database using DTS. Use the ALTER TABLE statement to change the column type to XML. • You can copy data in SQL Server 2000 using BCP OUT, insert data batch into the SQL Server 2005 database using BCP IN. • If you store the data in the relationship between the SQL Server 2000 database, create a new table with a NTEXT column while creating a primary key column in this table as needed to use as a line identifier. Use client programming to retrieve XML generated by for XML in the server and write it to NTEXT columns. Then, use the above trick to transfer the data to the SQL Server 2005 database. You can choose to write XML to the XML column of the SQL Server 2005 database.</p>
<p>Example: Change the column Type to XML Suppose You will need to change the [N] Text or Image Column XYZ in Table R to Nonclass XML. The following statement can perform such changes:</p>
<p>Alter Table R ALTER Column XYZ XML</p>
<p>• If necessary, you can type the target to XML by specifying an XML schema collection.</p>
<p>Batch Load XML data can load the XML data batch into the server in a bulk load function (such as BCP) in SQL Server. The data in the file can be loaded into the XML column through the OpenRowSet. The following example illustrates this.</p>
<p>Example: Loading an XML from a file This example shows how to insert a row in Table T. The value of the XML column is loaded from the file C: /yukon/xmlfile.xml as a CLOB, and the integer column is provided with a value of 10.</p>
<p>INSERT INTO TSELECT 10, XColfrom (SELK 'C: /YUKON/xmlfile.xml', Single_Clob) AS XCOL) AS R (Xcol)</p>
<p>Text encoding SQL Server 2005 stores XML data with Unicode (UTF-16). UTF-16 encoding from the XML data retrieved from the server; if you need a different encoding, you need to perform the necessary conversions on the retrieved data. Sometimes you may have a different encoded XML data, so you need to be very careful during the data loading process:</p>
<p>• If the text XML uses Unicode (UCS-2, UTF-16), it is assigned to the XML column, the variable, or parameters will not bring any problems. • If the encoding is not Unicode and is implicit (due to the source code page), the string code page in the database should be the same or compatible with the code points to be loaded (using collate when necessary). If there is no such server code page, you must add an explicit XML declaration with the correct encoded. • To use explicitly encoded, use the varbinary () type (it does not interact with the code page) or use the string type of the appropriate code page. Then, the data is assigned to the XML column, the variable, or parameters. Example: Explicitly specifying the encoding assumption that you have an XML document (VCDOC) is stored as VARCHAR (MAX) without explicit XML declaration. The following statement will add an XML declaration with encoding "ISO8859-1", connect the XML document to Varbinary (Max) to retain the byte representation, and finally convert it to XML. This allows the XML processor to analyze data in accordance with the specified "ISO 8859-1", and generate the corresponding UTF-16 representation for string values.</p>
<p>SELECT CAST (('<? Xml Version = "1.0" encoding = "ISO8859-1"?>' Vcdoc) as varbinary (max)) AS XML)</p>
<p>XQuery and Type Reasoning Embed an XQuery (http://www.w3.org/tr/xquery/) language in T-SQL support query XML data type. The language is developing by the WWW Federation (W3C) (when writing this article), and all major database vendors (including Microsoft) are involved in development. It includes XPath 2.0 as a navigation language. At the same time, data modification language constructs for XML data types are also provided. For information on the XQuery constructor, function, and operators supported in SQL Server 2005, see the online book.</p>
<p>The error model has a syntax error XQuery expression and an XML DML statement returns a compilation error. The compilation phase checks the statically type correctness of the XQuery expression and the DML statement, and the type of XML schema is used for type XML. If an expression may fail during the runtime, it will cause a static type error. An example of static errors has a string to an integer and query the type data in a node that does not exist.</p>
<p>Unlike the W3C standard, the XQuery runtime error is converted to an empty sequence that can be propagated as empty XML or NULL to query results (depending on the call context).</p>
<p>By explicitly converting to the correct type, users can avoid static errors, although the runtime conversion error will be converted to empty sequence.</p>
<p>The following subsections will discuss the type check in detail.</p>
<p>Uniqueness check If the compiler cannot determine if uniqueness can be guaranteed at runtime, the location step, function parameters, and operators (e.g., EQ) will return errors. The problem often appears on non-type data. For example, attribute lookups requires unique parent elements; you can choose a single parent node to meet the needs. Calculate Node () - value () combinations (see Value (), Nodes (), and OpenXML ()) to extract attribute values, which may not require a specified number, as shown below.</p>
<p>Example: The known uniqueness is in this example, the nodes () method generates a separate row for each element. (For detailed instructions on the Nodes () method, see Value (), NODES (), and OpenXML ()). The value () method for evaluating value on the node will extract @genre (it serves as a uniqueness as attribute). Select Nref.Value ('@ genre', 'varchar (max)) LastnameFrom T cross Apply Xcol.nodes (' // Book ') AS R (NREF)</p>
<p>The XML architecture is used to check the type XML. If the node is specified as a unique node in an XML schema, the compiler will use this information and will not appear any errors. Otherwise, you need to select the serial number of a single node. In particular, if the child or its own shaft (//), such as / book // title, the uniqueness of the <title> element is lost, even if the XML architecture specifies its nature. Please rewrite it as (/ Book // Title) [1].</p>
<p>For type checks, you need to remember the difference between // first-name [1] and (// first-name) [1]. The former returns a sequence of nodes, of which each node is the leftmost node in its meticulous node. The latter returns the first unique node in the XML instance.</p>
<p>Example: Value () Usage The query executed on the non-type XML column will cause static, compile errors, because value () requires a unique node as the first parameter, but the compiler cannot determine if it is running Will only have a node:</p>
<p>Select Xcol.value ('// Author / Last-Name', 'NVARCHAR (50)') LastnameFrom T</p>
<p>You may try the following solutions:</p>
<p>Select Xcol.Value ('// Author / Last-Name [1],' NVARCHAR (50) ') LastnameFrom T</p>
<p>However, this will not correct the error because multiple nodes may appear in each XML instance. The following rewriting methods will be effective:</p>
<p>Select Xcol.value ('(// author / last-name) [1]', 'NVARCHAR (50)') LastnameFrom T</p>
<p>This Query Returns The Value of The First Element in Each XML Instance.</p>
<p>If the parent shaft cannot be determined, it will become anytype, and the latter does not imply converted to any other type. This happens during navigation using the parent shaft (eg, Xcol.Query ('/ Book / @ Genre /../ Price'); this parent node type is determined to be anytype. Elements may also be defined as AnyType in the XML architecture. In both cases, the more accurate type information is often caused by static type errors, and requires the atom value explicitly converted to their specific types.</p>
<p>Data (), text () and string () Accessor XQuery have a function of the type of type, type value, a function of type, a node test text () that can return text nodes, and can be returned The function of the string value of the node fn: string (). They usage sometimes cause confusion. Below is the criteria for correct use of them in SQL Server 2005. Consider XML Example 12: Non-Type XML: Path Expression / AGE / TEXT () Returns the text node "12". The same is true for the string value "12", FN: String (/ Age). ? Type XML: For any simple type of element, expression / age / text () will return static errors. On the other hand, fn: data (/ agn) returns an integer 12, while fn: string (/ agn) generates a string "12".</p>
<p>The combined type of function and operator is taken seriously due to type inspection, the joint type requirements. The following examples illustrate two problems.</p>
<p>Example: Functions on a federated type Consider the following federated <R> element definition</p>
<p><xs: element name = "r"> <xs: simpletype> <xs: union meionpes = "xs: int xs: float xs: double" /> </ xs: simpletype> </ xs: element></p>
<p>In the XQuery context, "average" function fn: avg (// r) will return static errors because the XQuery compiler cannot be different types of elements in the parameters of Fn: AVG () (xs: int, xs: float or xs : Double) value. To solve this problem, turn the function call to Fn: AVG (for $ R IN // R Return $ R CAST AS: Double?).</p>
<p>Example: The operator adds operation " " on the federated type " " requires an accurate operand type, so that the expression (// r) [1] 1 returns a static error on the type definition of the above elements. One way to resolve this issue is (// r) [1] Cast AS: INT? 1, "?" Indicates that the specific value 0 or 1. SQL Server 2005 requires "?" "CAST AS" because any conversion creates an empty sequence due to runtime errors.</p>
<p>Value (), NODES (), and OpenXML () can use multiple value () methods to generate a rowset of the extraction value for the XML data type in the SELECT clause. The Nodes () method generates an internal reference for each node selected for further query. When there is a plurality of columns, and when the path expression for generating the rowset may be more complicated, combining the nodes () and value () methods can be more efficient.</p>
<p>The nodes () method can generate an instance of a special XML data type, each instance sets its context to the selected node. Such XML instances support query (), value (), nodes (), and exist () methods, and can be used in count (*) aggregation. All other usages will cause errors.</p>
<p>Example: Nodes () Usage Suppose You want to extract the name is not the name of the author of "David", as a rowset consisting of two columns (firstname and lastname). This purpose can be achieved using the nodes () and value () methods, as shown below:</p>
<p>SELECT NREF.VALUE ('first-name [1],' nvarchar (50) ') firstname, NREF.VALUE (' last-name [1], 'nvarchar (50)') LastnameFrom T Cross Apply Xcol.nodes ('// author') AS R (NREF) where nref.exist ('. [first-name! = "David"]) = 1 In this example, nodes (' // author ') will generate a A rowset consisting of references to each XML instance element. The name and last name of the author can be obtained by evaluating the value () method relative to these references.</p>
<p>SQL Server 2000 provides features that use OpenXML () from an XML instance to generate rows. You can specify the relational architecture of the rowset and specify how the values inside the XML instance map to the columns in the row.</p>
<p>Example: Using OpenXML () We can use OpenXML () to change the query in the previous example, the method is to create a cursor, read the XML instance into an XML variable, and then It applies OpenXML ():</p>
<p>DECLE NAME_CURSOR CURSORFOR SELECT XCOL from Topen Name_cursordeclare @Xmlval Xmldeclare @IDOC INTFETCH NEXT from Name_Cursor Into @xmlval</p>
<p>WHILE (@@ FETCH_STATUS = 0) BEGIN EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlVal SELECT * FROM OPENXML (@idoc, '// author') WITH (FirstName varchar (50) 'first-name', LastName varchar (50) ' Last-name ') r where r.firstname! =' DAVID '</p>
<p>EXEC SP_XML_REMOVEDOCUMENT @idoc fetch next from name_cursor @xmlvalendclose name_cursordeallocate Name_Cursor</p>
<p>OpenXML () creates a representation in memory and uses a worksheet instead of a query processor. It relies on the XPath 1.0 processor of MSXML 3.0 instead of the XQuery engine. The worksheet is not shared in multiple calls to OpenXML () (even on the same XML instance). This limits its scalability. When the WITH clause is not specified, the edge table format of XML data can be accessed via OpenXML (). Moreover, it is also possible to use the remainder in a separate "overflow" column using the XML value.</p>
<p>The combination of nodes () and value () functions can effectively use the XML index. Therefore, this combination can exhibit higher scalability than OpenXML.</p>
<p>Use the for XML to generate XML from the row to generate an XML data type instance using the For XML from the row.</p>
<p>The result can be assigned to the XML data type column, variables, or parameters. Moreover, the FOR XML can be nested to generate any hierarchy. This makes nested for XML more easily than for XML Explicit, but for deeper hierarchies, its performance may not be very good. For XML also introduces a new Path mode that specifies which path to the XML tree should appear in the value of the XML tree. You can use the new FOR XML Type directive to define the read-only XML view on the relational data via SQL syntax. This view can be queried by SQL statements and embedded xQuery, as shown below. For example, you can reference such SQL views during the stored procedure.</p>
<p>Example: Returns the SQL view below the generated XML data type The following SQL view definition can create an XML view on a relationship column (PK) and a book author retrieved from an XML column:</p>
<p>Create View V (Xmlval) Asselect PK, Xcol.Query ('/ Book / Author') from tfor XML Auto, Type</p>
<p>The view v contains a row, the row has only one column: XML type XMLVALTYPE. You can query it as an instance of a regular XML data type instance. For example, the following query returns the name of the author "David" is:</p>
<p>SELECT XMLVAL.QUERY ('// author [first-name = "david"] ") from v</p>
<p>SQL view defines an XML view created to some extent similar to an XML view created with an annotation schema. However, there is an important difference between the two. The SQL view definition is read-only and must be operated by embedded xQuery; and an XML view with an annotation architecture is not the case. Moreover, the SQL view generates an XML result before applying the XQuery expression, and the XPath query on the XML view calculates the SQL query on the base form.</p>
<p>Adding business logic can be added to XML data in a variety of ways:</p>
<p>• You can write a bank or column constraint to implement a domain-specific constraint during inserting and modifying XML data. • You can write the corresponding trigger on the XML column to cause it when you insert or update a value in this column. The trigger can include a domain-specific verification rule or a fill the attribute table. • You can write the SQLCLR function using the hosted code and deliver the XML value, and use the XML processing functionality provided by the System.xml namespace. An example of this is to apply XSL conversions to XML data, as shown below. You can also turn an XML insequence into one or more managed classes, and use managed code to operate them. • You can write the T-SQL stored procedures and functions to activate the processing on the XML column to meet your business needs.</p>
<p>Example: Apply XSL Conversion Considering the CLR function transformxml (), it accepts an XML data type instance and a XSL conversion stored in the file, which applies the conversion to XML data, and returns the converted XML after the result. The bactercy function written in C # is as follows:</p>
<p>public static SqlXml TransformXml (SqlXml XmlData, string xslPath) {Load XSL transformation XslTransform xform = new XslTransform () //; XPathDocument xslDoc = new XPathDocument (xslPath); xform.Load (xslDoc.CreateNavigator (), null);</p>
<p>// Load XML data XPathDocument xDoc = new XPathDocument (XmlData.CreateReader ()); XPathNavigator nav = xDoc.CreateNavigator (); // Return the transformed value SqlXml retSqlXml = new SqlXml (xform.Transform (nav, null)); return (retsqlxml);</p>
<p>After registering this assembly, and created a user-defined T-SQL function SQLXSLTRANSFORM () corresponding to TransformXML (), the function can be called from the T-SQL as in the query below:</p>
<p>Select Sqlxsltransform (Xcol, 'C: /yukon/xsltransform.xsl') from twhere xcol.exist ('/ book / title / text () [Contains (., "Custom")]) = 1</p>
<p>The query result contains the row of XML after conversion.</p>
<p>SQLCLR opens a new world that can be used to decompose XML data into tables or attributes, and use the hosted class in the System.xml namespace to query XML data. For more information, see SQL Server 2005 and Microsoft Visual Studio "Whidbey" online books.</p>
<p>Back to top Cross-domain query When your data resides in a relationship column and an XML data type column, you may want to write a query that combines relational data processing and XML data processing. For example, you can use for XML to convert the data in the relationship column and the XML column to an XML data type instance, and then use XQuery to query it. Instead, a rowset can be generated from the XML value (see Usage) and query it using T-SQL.</p>
<p>The more convenient and effective way to write cross-domain queries is to use SQL variables or columns in XQuery or XML DML expression:</p>
<p>• You can use the value of the SQL variable in the xQuery or XML DML expression, by SQL: variable (). • You can use the value in the relationship column in the XQuery or XML DML expression, by SQL: Column (). • This method allows the application to parameterize the query, as shown in the example below. However, it is not allowed to use XML and user-defined types in SQL: Variable () and SQL: Column ().</p>
<p>Example: Using SQL: Variable () Cross-domain query The following query is an example: the query displayed in the query on the calculation column of the XML data type method is modified. In this release, use SQL variable @isbn to incur interested ISBN. You can search for any ISBN by replacing constants with SQL: Variable (), you can use this query to search any ISBN, not just the one of its ISBN is 0-7356-1588-2.</p>
<p>Declare @isbn varchar (20) set @isbn = '0-7356-1588-2'select xcolfrom twhere xcol.exist (' / book [@isbn = SQL: variable ("@ ISBN")] ') = 1</p>
<p>SQL: Column () can be used in a similar manner and provide additional benefits. You can use the index on the column to increase efficiency, which is determined by the cost-based query optimizer. Moreover, the calculation column can store the promotion attribute, as described in the computing column based on the XML data type. Back to top Directory view for native XML support? The purpose of the directory view is to provide metadata information related to the XML usage. Several directory views are discussed below.</p>
<p>The XML index XML index item appears in the directory view sys.indexes, the index "Type" is 3. "Name" column contains the name of the XML index.</p>
<p>The XML index is also recorded in the directory view sys.xml_indexes, which contains all columns of sys.indexes and some special columns for XML indexes. The value NULL in the "secondary_type" represents the primary XML index; value "p", "r", and "V '" represent the PATH, PROPERTY, and VALUE assist XML indexes, respectively.</p>
<p>The spatial utilization rate of the XML index can be found in the table value function sys.fn_indexinfo (). This function provides a number of information, such as other information, number of disk pages, average rows (bytes), record numbers, and all index types (including XML index). This information is provided for each database partition; the XML index uses the same partition scheme and partition function of the base table.</p>
<p>Example: Space Utilization of XML Index Select Sum (Pages) from sys.fn_indexinfo ('T', 'IDX_XCOL_PATH', Default, 'Detailed')</p>
<p>This generates the number of XML index idx_xcol_paths in all partitions in all partitions. If the SUM () function is not used, the result will return the disk page utilization of each partition.</p>
<p>Retrieving XML Architecture Set XML Architecture Set in Directory View Sys.xml_schema_collections is enumerated. The XML architecture set "SYS" is defined by the system, which contains a predefined namespace that can be used in all user-defined XML architectures collection without explicit loading. This list contains XML, XS, XSI, FN, and XDT namespaces. The other two dedicated directories are: sys.xml_schema_namespaces, which enumerates all namespaces in each XML schema collection; sys.xml_components, which enumerate all XML architecture components in each XML architecture.</p>
<p>Built-in function XML_SCHEMA_NAMESPACE (ScheManame, XmlschemacolectionName, Namespace-URI) can generate an XML data type instance that includes an XML architecture segment containing architecture contained in the XML architecture (except for predefined XML architectures).</p>
<p>You can use the following to enumerate the contents of the XML architecture collection:</p>
<p>• Write the T-SQL query on the appropriate directory view of the XML architecture collection. • Use the built-in function XML_SChema_NameSpace (). The XML data type method can be applied to the output of the function. However, you cannot modify the underlying XML architecture. The following example sets these concepts.</p>
<p>Example: Enumerate XML Namespace in the XML Architecture Collection For XML Architecture Collection "MyCollection" using the following query:</p>
<p>SELECT XSN.nameFROM sys.xml_schema_collections XSC JOIN sys.xml_schema_namespaces XSN ON (XSC.xml_collection_id = XSN.xml_collection_id) WHERE XSC.name = 'myCollection' Example: enumerate the contents of the XML schema collection enumerated the following statement in a relational schema dbo The XML architecture set "MyCollection" content.</p>
<p>SELECT XML_SCHEMA_NAMESPACE (n'dbo ', n'mycollection')</p>
<p>By specifying the target namespace as the third parameter of XML_SChema_NameSpace (), you can get a separate XML architecture in the set by the XML data type instance, as shown below.</p>
<p>Example: Output XML Architecture Collection The following statement below the XML architecture in the relational architecture DBO The XML architecture in "MyCollection" is "http://www.microsoft.com/books" XML architecture.</p>
<p>SELECT XML_SCHEMA_NAMESPACE (n'dbo ', n'mycollection ", n'http: //www.microsoft.com/books')</p>
<p>Query the XML architecture If you need to query the XML architecture that has been loaded into the XML architecture collection, you can use the following:</p>
<p>• Write the T-SQL query on the directory view of the XML architecture name. • In addition to loading an XML architecture into an XML type system, a table containing the XML data type column is created to store an XML architecture. You can use the XML data type method to query the XML column. Moreover, you can generate an XML index on this column. However, it is necessary to maintain the consistency between the XML architecture stored in the XML column and the XML schema stored in the XML type system. For example, if you delete an XML architecture namespace from an XML type system, you must also remove the namespace from the table to keep consistency.</p>
<p>Go to the original English page</p></div><div class="text-center mt-3 text-grey">
转载请注明原文地址:https://www.9cbs.com/read-6282.html</div><div class="plugin d-flex justify-content-center mt-3"></div><hr><div class="row"><div class="col-lg-12 text-muted mt-2"><i class="icon-tags mr-2"></i><span class="badge border border-secondary mr-2"><h2 class="h6 mb-0 small"><a class="text-secondary" href="tag-2.html">9cbs</a></h2></span></div></div></div></div><div class="card card-postlist border-white shadow"><div class="card-body"><div class="card-title"><div class="d-flex justify-content-between"><div><b>New Post</b>(<span class="posts">0</span>)
</div><div></div></div></div><ul class="postlist list-unstyled">
</ul></div></div><div class="d-none threadlist"><input type="checkbox" name="modtid" value="6282" checked /></div></div></div></div></div><footer class="text-muted small bg-dark py-4 mt-3" id="footer"><div class="container"><div class="row"><div class="col">CopyRight © 2020 All Rights Reserved
</div><div class="col text-right">Processed:
<b>0.044</b>, SQL:
<b>9</b></div></div></div></footer><script src="./lang/en-us/lang.js?2.2.0"></script><script src="view/js/jquery.min.js?2.2.0"></script><script src="view/js/popper.min.js?2.2.0"></script><script src="view/js/bootstrap.min.js?2.2.0"></script><script src="view/js/xiuno.js?2.2.0"></script><script src="view/js/bootstrap-plugin.js?2.2.0"></script><script src="view/js/async.min.js?2.2.0"></script><script src="view/js/form.js?2.2.0"></script><script>
var debug = DEBUG = 0;
var url_rewrite_on = 1;
var url_path = './';
var forumarr = {"1":"Tech"};
var fid = 1;
var uid = 0;
var gid = 0;
xn.options.water_image_url = 'view/img/water-small.png';
</script><script src="view/js/wellcms.js?2.2.0"></script><a class="scroll-to-top rounded" href="javascript:void(0);"><i class="icon-angle-up"></i></a><a class="scroll-to-bottom rounded" href="javascript:void(0);" style="display: inline;"><i class="icon-angle-down"></i></a></body></html><script>
var forum_url = 'list-1.html';
var safe_token = 'Nlj5yBYAyCQAp3uYXD_2Bop8V1zn06BGjdBbY7vi0pVmbJFub2y_2FOmehjRxwCmVmyrE9sFCiL3N4oyMK6iBnxeCg_3D_3D';
var body = $('body');
body.on('submit', '#form', function() {
var jthis = $(this);
var jsubmit = jthis.find('#submit');
jthis.reset();
jsubmit.button('loading');
var postdata = jthis.serializeObject();
$.xpost(jthis.attr('action'), postdata, function(code, message) {
if(code == 0) {
location.reload();
} else {
$.alert(message);
jsubmit.button('reset');
}
});
return false;
});
function resize_image() {
var jmessagelist = $('div.message');
var first_width = jmessagelist.width();
jmessagelist.each(function() {
var jdiv = $(this);
var maxwidth = jdiv.attr('isfirst') ? first_width : jdiv.width();
var jmessage_width = Math.min(jdiv.width(), maxwidth);
jdiv.find('img, embed, iframe, video').each(function() {
var jimg = $(this);
var img_width = this.org_width;
var img_height = this.org_height;
if(!img_width) {
var img_width = jimg.attr('width');
var img_height = jimg.attr('height');
this.org_width = img_width;
this.org_height = img_height;
}
if(img_width > jmessage_width) {
if(this.tagName == 'IMG') {
jimg.width(jmessage_width);
jimg.css('height', 'auto');
jimg.css('cursor', 'pointer');
jimg.on('click', function() {
});
} else {
jimg.width(jmessage_width);
var height = (img_height / img_width) * jimg.width();
jimg.height(height);
}
}
});
});
}
function resize_table() {
$('div.message').each(function() {
var jdiv = $(this);
jdiv.find('table').addClass('table').wrap('<div class="table-responsive"></div>');
});
}
$(function() {
resize_image();
resize_table();
$(window).on('resize', resize_image);
});
var jmessage = $('#message');
jmessage.on('focus', function() {if(jmessage.t) { clearTimeout(jmessage.t); jmessage.t = null; } jmessage.css('height', '6rem'); });
jmessage.on('blur', function() {jmessage.t = setTimeout(function() { jmessage.css('height', '2.5rem');}, 1000); });
$('#nav li[data-active="fid-1"]').addClass('active');
</script>
