SQLServer2005分解并导入xml文件
SQLServer2005分解并导入xml文件 收藏
测试环境SQL2005，windows2003
DECLARE @idoc int;
DECLARE @doc xml;
SELECT @doc=bulkcolumn from OPENROWSET(
BULK 'D: \test.xml',
SINGLE_BLOB) AS x
EXEC sp_xml_preparedocument @Idoc OUTPUT, @doc
SELECT * into #temp from OPENXML (@Idoc, '/Root/Item',2)
WITH (
[ID] varchar(10)
,[Name]varchar(10)
,[Caption]varchar(10)
)
select * from #temp
drop table #temp
/**//*--文件D: est.xml的文本内容
<?xml version="1.0" encoding="UTF-8"?>
<Root>
<Item>
<ID>1</ID>
<Name>jinjazz</Name>
<Caption>剪刀</Caption>
</Item>
<Item>
<ID>2</ID>
<Name>zswang</Name>
<Caption>伴水</Caption>
</Item>
</Root>
*/
/**//*---查询结果
ID Name Caption
---------- ---------- ----------
1 jinjazz 剪刀
2 zswang 伴水
*/
相关文档：
XML How to Program
Beginning Xml Databases
Beginning XSLT and XPath Transforming XML Documents and Data
ASP.NET 2.0 XML
XML 手册 4th Edition
XML Schema Complete Reference
......
function getkeyvalue(s_keyname,s_keystr)
s_keybegin="<"+s_keyname+">"
s_keyend="</"+s_keyname+">"
s_i_begin=instr(s_keystr,s_keybegin)
s_i_end=instr(s_keystr,s_keyend)
if s_i_end<=s_i_begin+len(s_keybegin) then
getkeyvalue=""
exit function
else
getkeyvalue=mid(s_k ......
这篇文章被转载的次数最多，其实代码简陋得我自己都看不下去。只不过发表这篇文章时很多人需要这个功能。
这几天写个数据库查询分析器，要用到XML记录用户注册的数据库连接地址、端口等信息，最开始想用java ......
if (object_id ('t' ) is not null ) drop table t
go
create table t (id int identity (1 , 1 ), name varchar (40 ))
go
insert into t (name ) select newid ()
go 10
select * from t
/*
1 18C1C418-9029-4599-8D5E-616354A113C8
2 A0FE1177-09D8-4C56-9FB5-C2FA ......
private
NodeList root(
final
String url ,
final
String str){
NodeList root =
null
;
try
{
InputSource is=
new
InputSource(
new
InputStreamReader(
new
UR ......
