SQL Server 中对XML数据的五种基本操作代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】 SQL Server 中对XML数据的五种基本操作

作者:tjvictor / 发布于2014/10/9/ 712


	declare @XMLVar xml = '

	<catalog>

	 <book category="ITPro">

	 <title>Windows Step By Step</title>

	 <author>Bill Zack</author>

	 <price>49.99</price>

	 </book>

	 <book category="Developer">

	 <title>Developing ADO .NET</title>

	 <author>Andrew Brust</author>

	 <price>39.93</price>

	 </book>

	 <book category="ITPro">

	 <title>Windows Cluster Server</title>

	 <author>Stephen Forte</author>

	 <price>59.99</price>

	 </book>

	</catalog>'

	1. xml.exist

	 select @XMLVar.exist('/catalog/book')-----返回1

	 select @XMLVar.exist('/catalog/book/@category')-----返回1

	 select @XMLVar.exist('/catalog/book1')-----返回0

	 set @XMLVar = null

	 select @XMLVar.exist('/catalog/book')-----返回null

	

	2.xml.value

	 select @XMLVar.value('/catalog[1]/book[1]','varchar(MAX)')

	 select @XMLVar.value('/catalog[1]/book[2]/@category','varchar(MAX)')

	 select @XMLVar.value('/catalog[2]/book[1]','varchar(MAX)')

	 结果集为：

	 Windows Step By StepBill Zack49.99 Developer NULL

	

	3.xml.query

	 select @XMLVar.query('/catalog[1]/book')

	 select @XMLVar.query('/catalog[1]/book[1]')

	 select @XMLVar.query('/catalog[1]/book[2]/author')

	 结果集分别为：
	<book category="ITPro">
	 <title>Windows Step By Step</title>
	 <author>Bill Zack</author>
	 <price>49.99</price>
	</book>
	<book category="Developer">
	 <title>Developing ADO .NET</title>
	 <author>Andrew Brust</author>
	 <price>39.93</price>
	</book>
	<book category="ITPro">
	 <title>Windows Cluster Server</title>
	 <author>Stephen Forte</author>
	 <price>59.99</price>
	</book>

	
	<book category="ITPro">
	 <title>Windows Step By Step</title>
	 <author>Bill Zack</author>
	 <price>49.99</price>
	</book>

	
	<author>Andrew Brust</author>

	

	4.xml.nodes

	 select T.c.query('.') as result from @XMLVar.nodes('/catalog/book') as T(c)

	 select T.c.query('title') as result from @XMLVar.nodes('/catalog/book') as T(c)

	

	 结果集分别为：
	<book category="ITPro"><title>Windows Step By Step</title><author>Bill &hellip;&hellip;&hellip;&hellip;

	<book category="Developer"><title>Developing ADO .NET</title><author>Andrew &hellip;&hellip;&hellip;&hellip;
	<book category="ITPro"><title>Windows Cluster Server</title><author>Stephen &hellip;&hellip;&hellip;&hellip;

	
	<title>Windows Step By Step</title>
	<title>Developing ADO .NET</title>
	<title>Windows Cluster Server</title>

试试其它关键字

　XML　　XML数据　

同语言下

可能有用的

tjvictor贡献的其它代码(3)