How SQL Server reads XML files

SQLServer2000 makes it easier to use XML to export data, but it is troublesome to import and process XML data in SQLServer2000. If you refer to BooksOnline (BOL), you will find related entries, including OPENXML and OPENROWSET. All of these examples support using XML text as declared variables.

SQL Server 2000 makes it easier to export data in XML, but it is troublesome to import and process XML data in SQL Server 2000. If you refer to Books Online (BOL), you will find related entries, including OPENXML and OPENROWSET. All of these examples support using XML text as declared variables.

SQL Server 2000 makes it easier to export data in XML, but it is troublesome to import and process XML data in SQL Server 2000.

If you refer to Books Online (BOL), you will find related entries, including OPENXML and OPENROWSET. All of these examples support using XML text as declared variables, which is very convenient for users who often process text, this is not the case for developers who want to read and process XML files in development. To solve such a problem, it may be better to analyze it from the inside out to the outside.

OPENXML is a rowset function (that is, a rowset is returned), which works in a way similar to the rowset functions OPENQUERY and OPENROWSET. You can use OPENXML to perform JOINs operations on XML data without importing data first. You can also use the statements in combination with INSERT, SELECT, UPDATE, and DELETE operations. However, to use OPENXML, you must execute two tasks that are not required by OPENQUERY and OPENROWSET. The two tasks require two system storage processes. The first is sp_xml_preparedocument, which reads a specific XML text and extracts its content to the memory. The syntax is as follows:

Sp_xml_preparedocument @ hdoc = OUTPUT,

[, @ Xmltext =]

[, @ Xpath_namespaces =

The specific parameters are as follows: @ hdoc: The handle pointing to a memory area (equivalent to a pointer in terms of function). The related data is stored here. Note that this is an output variable. After the process runs, the variable contains a handle pointing to the XML file content in the memory address. Because you need to use this result later, make sure to save it; @ xmltext: the XML text you actually want to process; @ xml_namespaces: any namespace index (namespace references) required for normal operation of your XML data ). Note that any URL that appears here must be enclosed by Angle brackets (<>). If the parameters passed are valid and the XML document exists, then your XML data will be stored in the memory. Now you can call sp_xml_preparedocument, pass the variables that store the XML file, and execute OPENXML. Syntax:

OPENXML (idocint [in], rowpatternnvarchar [in], [flagsbyte [in])

[WITH (SchemaDeclaration | TableName)]

Note: there is not enough text in this article to describe the parameters received by OPENXML. For more information, refer to the BOL. Search openxml in the Transact-SQL Reference.

Now we have reached the final step. All the remaining work is to import an actual XML file to the SQL and process it. (It's amazing why this is not a key part in all the BOL examples ). (I must thank my colleague Billy Pang for his help. He helped me solve the problem and gave the code-although I have cut the code out of this article. Thank you, Billy !) The basic technique is to read files row-by-row by text. Connect all read rows to a large VARCHAR variable. Finally, pass the variable to the Code mentioned above.

The following code reads a file and stores its content in a variable:

DECLARE @ FileName varchar (255)

DECLARE @ ExecCmd VARCHAR (255)

DECLARE @ y INT

DECLARE @ x INT

DECLARE @ FileContents VARCHAR (8000)

Create table # tempXML (pk int not null identity (1, 1), ThisLine VARCHAR (255 ))

SET @ FileName = 'C: TempCurrentSettings. xml'

SET @ ExecCmd = 'type' + @ FileName

SET @ FileContents =''

Insert into # tempXML EXEC master. dbo. xp_mongoshell @ ExecCmd

SELECT @ y = count (*) from # tempXML

SET @ x = 0

WHILE @ x <> @ y

BEGIN

SET @ x = @ x + 1

SELECT @ FileContents = @ FileContents + ThisLine from # tempXML WHERE PK

= @ X

END

SELECT @ FileContents as FileContents

Drop table # tempXML

Now you have obtained all the file content in the variable @ FileContents. All you need to do is pass the variable to sp_xml_preparedocument through the @ xmltext parameter, and then call OPENXML.

With this solution, it is possible to process XML documents. You can connect XML documents with SQL tables without importing data, and then perform INSERT, PDATE, DELETE, and other operations on the data.