我如何读取xml值并使用sql server存储过程将该值存储到我的数据库中 [英] how can i read xml values and store that values into my database using sql server stored procedure
问题描述
想要使用存储过程获取XML格式的XML。我得到的值类似于下面提到的格式。我想要做的是只在数据库中创建一个表作为'RecordsDB'并使用存储过程解析XML,在'Records'中插入值。我试图在堆栈溢出和网络上搜索这样的问题,但没有得到答案,只知道迷你方式是读取节点值,如line.column.value(something)。但我确实有来自ASP.NET网络服务的数千条记录。*
< Root> (根节点)
< Record> (辅助节点)
< Name> A< / Name> (子节点1)
<年龄> 21< / Age> (子节点2)
< / Record>
<记录>
< Name> b< /名称>
<年龄> 22< / Age>
< /记录>
< / Root>
如何完成任务?谢谢。
你可以使用内置的存储过程...
DECLARE @ idoc int , @doc varchar ( 1000 );
SET @ doc = '
< root>
< record>
< name>一个< / name>
< age> 21< / age>
< / record>
< record>
< name> b< / name>
< age> 22< / age>
< / record>
< / root>'
- 创建内部代表XML文档。
EXEC sp_xml_preparedocument @ idoc 输出, @ doc ;
- 执行使用OPENXML行集提供程序的SELECT语句。
SELECT * FROM OPENXML ( @ idoc ,' / Root / Record', 2 ) WITH (名称 varchar ( 10 ),年龄 Int );
结果:
名称年龄
- - -----
A 21
b 22
Want to get values Coming in XML format using stored procedure. The values i am getting are like in the format as mentioned below . What i am trying to do is to just create a table in database as 'RecordsDB' and using Stored procedure parsing the XML, inserts the value in 'Records'. I am trying to search question like this on stack overflow and net but not getting answer ,only knows the mini way is to read the node value like line.column.value(something). But i do have some thousands of records coming from ASP.NET web service.*
<Root> (Root node) <Record> (Secondary node) <Name> A </Name> (Child node1) <Age> 21 </Age> (Child node2) </Record> <Record> <Name> b </Name> <Age> 22 </Age> </Record> </Root>
How to meet the task? Thanks.
U can use the inbuilt Stored Procedure...
DECLARE @idoc int, @doc varchar(1000); SET @doc =' <root> <record> <name> A </name> <age> 21 </age> </record> <record> <name> b </name> <age> 22 </age> </record> </root>' --Create an internal representation of the XML document. EXEC sp_xml_preparedocument @idoc OUTPUT, @doc; -- Execute a SELECT statement that uses the OPENXML rowset provider. SELECT * FROM OPENXML (@idoc, '/Root/Record',2) WITH (Name varchar(10),Age Int);
Result:
Name Age ---- ----- A 21 b 22
这篇关于我如何读取xml值并使用sql server存储过程将该值存储到我的数据库中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!