How to read xml files in mssqlserver

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.

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:

The Code 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 (which is 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 want to process;

@ Xml_namespaces: Any namespace index (namespace references) required for normal operation of your XML data ). Note that any URL shown here must be enclosed by Angle brackets (<>;

Assume that all these parameters are valid and the XML document exists, 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:

The Code is as follows:
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:

The Code is as follows:

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.