将xml保存回数据库 [英] saving xml back to database

问题描述

大家好，

我有一个格式正确的xml文件，我想将该文件保存到数据库表中，并且列名将成为xml文件中的标记名.

请帮助我如何实现

谢谢


问候

Hello all,

I have a well formatted xml file and I wanna save that file to the database table and the columns names going to be the tag names in xml file.

Kindly help me how can i acheive it

thanks


regards

推荐答案

如果要将其另存为字符串以外的其他内容，则可以在SQL Server中使用OpenXML将XML转换为表并在其中运行SQL它插入它.或者，您可以编写代码将XML分解为使用SQL保存的数据.

If you want to save it as anything other than a string, you can use OpenXML in SQL Server to transform your XML in to a table and run SQL on it to insert it. Or you can write code to break your XML in to data that you save with SQL.

尝试类似的方法.

我的桌子

Try similar to this.

My table

create table xmltest
([Server] varchar(100),
[Database] varchar(100)
)

我在路径"C:\\ XMLFile1.xml"中有一个xml

XML内容是

I have a xml in the path "C:\\XMLFile1.xml"

XML content is

<?xml version="1.0" standalone="yes" ?>
- <NewDataSet>
- <Table1>
  <Server>Server1</Server>
  <Database>Database1</Database>
  </Table1>
- <Table1>
  <Server>Server2</Server>
  <Database>Database2</Database>
  </Table1>
- <Table1>
  <Server>Server3</Server>
  <Database>Database3</Database>
  </Table1>
  </NewDataSet>

现在我存储的proc

Now my stored proc

CREATE PROCEDURE sp_InsertData
AS
BEGIN
   
    DECLARE @xmlHandle INT 
    declare @xmlString VARCHAR(MAX)
    --specify the xml path which you want to read
    Select  @xmlstring=CAST(x AS varchar(max))
			FROM OPENROWSET(BULK 'C:\\XMLFile1.xml',single_clob)  AS T(x)
    
    
    EXEC sp_xml_preparedocument @xmlHandle output, @xmlString 
--insert into table
   Insert into xmltest 
	Select [Server],[Database]
		FROM OPENXML (@xmlHandle, '/NewDataSet/Table1',2)
		--Specify actual node name.
			 with ([Server] varchar(100),[Database] Varchar(100))
    

    EXEC sp_xml_removedocument @xmlHandle   
   
END

现在执行我存储的proc:

Now execute my stored proc:

exec sp_InsertData

立即检查表

Check table now

select * from xmltest

这篇关于将xml保存回数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！