分解XML
来源:互联网 发布:nginx配置二级目录 编辑:程序博客网 时间:2024/05/16 01:04
分解XML
DECLARE @xmlvar xml
set @xmlvar = '
<Data>
<Student id="s1">
<Name>Bugs Bunny</Name>
<Class>FFA</Class>
</Student>
<Student id="s2">
<Name>Road Runner</Name>
<Class>Sports</Class>
</Student>
<Student id="s3">
<Name>Wile E. Coyote</Name>
<Class>Science</Class>
</Student>
<Student id="s4">
<Name>Foghorn Leghorn</Name>
<Class>Self Defense</Class>
</Student>
</Data>'
--1
SELECT
A.student.value('@id','varchar(10)') AS id,
A.student.value('Name[1]','varchar(20)') AS Name,
A.student.value('Class[1]','varchar(20)') AS Class,
row_number() over (order by getdate()) AS ndx
FROM @xmlvar.nodes('/Data/Student') AS A(student)
--2
SELECT
A.student.value('@id','varchar(10)') AS id,
A.student.value('Name[1]','varchar(20)') AS Name,
A.student.value('Class[1]','varchar(20)') AS Class,
A.student.value('for $i in . return count($i/../*[.<<$i])+1','int') AS ndx
FROM @xmlvar.nodes('/Data/Student') AS A(student)
--3
DECLARE @docHandle int
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlvar
SELECT * ,row_number() over (order by getdate()) AS ndx
FROM OPENXML(@docHandle, N'/Data/Student/Class')
WITH (id char(2) '../@id',name varchar(20) '../Name',class varchar(20) '../Class' )
EXEC sp_xml_removedocument @docHandle
--结果
/*
id Name Class ndx
---------- -------------------- -------------------- -----------
s1 Bugs Bunny FFA 1
s2 Road Runner Sports 2
s3 Wile E. Coyote Science 3
s4 Foghorn Leghorn Self Defense 4
(4 行受影响)
id Name Class ndx
------- -------------------- -------------------- --------------------
s1 Bugs Bunny FFA 1
s2 Road Runner Sports 2
s3 Wile E. Coyote Science 3
s4 Foghorn Leghorn Self Defense 4
(4 行受影响)
id name class ndx
---- -------------------- -------------------- --------------------
s1 Bugs Bunny FFA 1
s2 Road Runner Sports 2
s3 Wile E. Coyote Science 3
s4 Foghorn Leghorn Self Defense 4
(4 行受影响)
*/
- 分解XML
- MSSQL2005 XML字段分解
- XML分解实例
- XML分解实例
- AndroidManifest.xml---分解
- SQLServer2005分解并导入xml文件
- SQLServer2005分解并导入xml文件
- SQLServer中用XQuery分解XML数据
- SQL Server2005中用XQuery分解XML数据
- SQLServer2005分解并导入xml文件
- 使用XML在MSSQL把字串分解
- Flex高性能xml递归分解
- 分解
- 从 DAD 到带注释的 XML 模式分解
- 在SQL Server中用XQuery分解XML数据
- 在SQL Server 2005中用XQuery分解XML数据
- 如何在SQL Server 2005中用XQuery分解XML数据
- 如何在SQL Server 2005中用XQuery分解XML数据
- VCL
- 位段
- TD试商用手机不回收,中国移动好大方!
- jdbc 连接数据库
- SQLite简介
- 分解XML
- 软件开发者面试百问
- Kernel中断处理模型
- VC++小技巧之隐藏Console窗口
- ASP.NET3.5中jQuery调用WebServices
- WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=200
- 嵌套类 局部类
- ORA-07445: [ACCESS_VIOLATION] [_keltfill+86] [PC:0xBA78BA] [ADDR:0x0] [UNABLE_TO_READ] []
- Show一下我的一套音响裸系统