CN105488080A - NET based XML and SQLServer data exchange method - Google Patents

Abstract

The invention discloses a NET based XML and SQLServer data exchange method. A query result can be stored as an XML document by using a SELECT statement; a function of storing the XML document in a relational database table is provided by using an OPENxML statement; and the data exchange between SQLServer2,000 and XML is actually realized.

Description

Based on XML and the SQLServer method for interchanging data of NET

Technical field

XML and SQLServer method for interchanging data based on ADONET relates generally to Computer Database field.

Background technology

XML is the abbreviation of eXtenMbleMarkupLanguage, its full name is " extend markup language ", it define a kind of file layout, a kind of method of preserving data, its self descriptiveness is highly suitable for the exchanges data between heterogeneous database system, and this exchange is not premised on prespecified data structure definition, possesses very strong opening.In recent years, XML technology has been widely used in the various fields such as ecommerce, E-Government, web-based teaching.

SQLServer Database Systems are one of Database Systems of current main flow, in SQLServer2000, introduce the direct support to XML, when installation SQL database, just MSXML syntax analyzer (Msxml2.d11) is installed, be used for Analysis of X ML document, user by browser with URL SQL Server 2000, and Query Result is returned in browser in xml format show, also by OPENXML statement, the data of XML format can be put into SQLServer2000.

summary of the invention

Do not have to find the application materials about this system aspects by national patent retrieval.

The method is by upgrading same data fragments by a layering DLL (dynamic link library) or the relevant API Access of practical ADO.NET.Substantially, a DataSet object can be converted to XMLDOM object and be considered as XMLDOM subtree incorporating the set of some tables in DataSet Object table.

SQLServer2000 is a powerful Relational DBMS, it has increased the new features of many support XML newly, the Query Result of SQLServer2000 relational database being shown as XML document is directly use the ExecuteXmlReader method of SqlCom.mand to create an XmlReader by .NETDataProvider, in common SELECT statement, add what FORXML clause had come, Query Result is stored as XML document, instead of the result set on ordinary meaning.

The MODE (RAW, AUTO, EXPLICIT) of XML and the pattern (Schema) of XML-DATA can be specified while use FORXML clause, by specifying 3 kinds of XML patterns one of RAW, AUT0, EXPLICIT, the data with certain style information just can be returned.But FORXML also has its limitation: <a> can not use FORXML in subquery; <b>FORXML cannot be used for calculated column.

We can use the FORXML clause newly provided in SQLServer2000 retrieve SQL data and generate XML document below:

<1>RAW pattern: obtain Query Result and each row in result set be converted to xML element, with general identifier <row> as rubidium marking.

SELECTC．CustomerID，O.OrderID，O.OrderDateFROMCustomersC.OrdersOWHEREC.CustomerID=O.CustomerIDORDERBYC.CustomerIDFORXMLRAW

Here is partial results:

<rowCustomerID=”ALFKI’lOrderID=”10643”OrderDate=”1997—08—25T00：00：00l’/><rowCustomerID=”AROUT”OrderID=’110355”OrderDate=”1996-11-15T00：00：00”/>

<2>AUTO pattern: return Query Result with simple nested XML tree.In FROM clause, each table having at least row to be listed in SELECT clause is expressed as an XML element, and the row listed in SELECT clause are mapped to suitable characteristic of elements.

SELECTC．CustomerlD，O．OrderID，O．OrderDateFROMCustomersC．OrdersOWHEREC．CustomerID=O．CustomerIDORDERBYC．CustomerIDFORXMLAUT0

Here is partial results:

<CCustomerID=11ALFKI”>

<OOrderID=”10643”

OrderDate=”1997-08-25T00：0000l’/>

<3>EXPLICIT pattern: this is that complexity is also a kind of pattern that function is the most powerful the most.The shape of the XML tree of specifying explicit definition to obtain, thus the comprehensive control to return structure is provided.Each row (field) independently can be mapped as attribute or hand element, use this kind of pattern, require to write inquiry in the specific mode of one, so that the nested additional information about expecting is specified in display.

XML document is saved in during relational database wraps up in; One of main application of XML is exactly make exchanges data, after converting the data of different-format to XML document, the form converting other again to carries out storing or processing, and therefore XML document can be converted to the form of SQLServer2000, XML document is kept in the table of SQLServer2000.

In SQLServer2000, OPENXML statement provides the function of storing X ML document in relation database table.SQLServer2 ∞ O supports three kinds of basic skills representing XML in a database:

OPENXML, XML batch loads and Updategram.OPENXML is T-SQL function, and it is responsible for the data that insertion represents by XML document, and the content of OPENXML analyzing XML file is also expressed as row collection it, and like this, the record that row is concentrated just can be stored in database table.XML batch load (XMLBulkLoad) be a com component, SQLServer2000 can read the data in XML file with it, and according to XDR or XSD pattern copy data to database table and row in.Different with OPENXML, XML batch loads to be through to optimize and can be used for processing mass data.Updategram is an XML file, and it comprises the changed information that must be input in one or more database table.Except done change, Updategram also comprises optional map information, to associate the element in XML source and the row in database better.

Illustrate how to take out XML document content with OPENXML with an example below, and show its content in the mode of data query table:

DECLAREhdocint

DECLAREdecvarchar (4000) sets up the XML document of record's information one by one

SETdec=‘<users>

<user>

<userlD>1<／userlD>

<userName> Zhang San </userName>

<／user>

<user>

<userlD>2<／u§erlD>

<userName> Li Si </userName>

</user></usersGr eatT.GreaT.GT ' uses OPENXML to generate demographic data's row collection one by one

EXECsp—xml—preparedocument

hdocOUTPUT，dec

Do with demographic data's row collection of XML document one by one

For the form of SELECT statement inquiry

SELECT*FROM0PENXML

(hdoc，‘／users／user’)

WITH (person number int ' ./

UserID ', personnel's name varchar (30)

‘．／userName’)

Be disposed one by one, releasing memory space

EXECsp_xml—removedocument

hdoc

Output rusults is as follows:

Person number personnel name

L Zhang San

2 Li Sis.

Claims (5)

1. upgrade same data fragments based on XML and the SQLServer method for interchanging data of NET by a layering DLL (dynamic link library) or the relevant API Access of practical ADO.NET.

2. the Query Result of SQLServer2000 relational database being shown as XML document according to claim 1 item is directly use the ExecuteXmlReader method of SqlCom.mand to create an XmlReader by .NETDataProvider, in common SELECT statement, add what FORXML clause had come, Query Result is stored as XML document.

3. retrieve SQL data according to claim 2 with the FORXML clause newly provided in SQLServer2000 and generate XML document mainly two kinds of patterns: RAW pattern and aUTO pattern.

4. each row in result set is converted to xML element, with general identifier <row> as rubidium marking according to the RAW call by pattern Query Result in claim 3.

5. return Query Result according to the simple nested XML tree of claim 3; In FROM clause, each table having at least row to be listed in SELECT clause is expressed as an XML element, and the row listed in SELECT clause are mapped to suitable characteristic of elements.