34,593
社区成员
发帖
与我相关
我的任务
分享
********************* 导入 xml 文件
DECLARE @idoc int
DECLARE @doc varchar(1000)
--sample XML document
SET @doc ='
<root>
<Customer cid= "C1" name="Janine" city="Issaquah">
<Order oid="O1" date="1/20/1996" amount="3.5" />
<Order oid="O2" date="4/30/1997" amount="13.4">Customer was very satisfied
</Order>
</Customer>
<Customer cid="C2" name="Ursula" city="Oelde" >
<Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue
white red">
<Urgency>Important</Urgency>
Happy Customer.
</Order>
<Order oid="O4" date="1/20/1996" amount="10000"/>
</Customer>
</root>
'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/root/Customer/Order', 1)
WITH (oid char(5),
amount float,
comment ntext 'text()')
EXEC sp_xml_removedocument @idoc
------------------------------------------------------------------------
-- Author : HappyFlyStone
-- Date : 2009-08-05
-- Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
--
-- 本文探讨 SQL Server 2005 中内置的 XML 支持.
-- 第三篇旨在对路径表达式中的全文与缩写讲法及绝对与相对路径详细说明。
-- 本文来自happyflystone -CSDN博客,
-- 转载请标明出处:http://blog.csdn.net/happyflystone
------------------------------------------------------------------------
上节说Xpath时,小梁大侠看过说不够具体,所以今天呢再深入点并使用点例子来说明问题。把xquery放到后面进行介绍。
路径表达式分为绝对与相对路径两种。在说具体的路径表达前得先说一下路径表达式有全文拼写语法和缩写语法。比如如下表达式:
/child::exam/child::name
其实可以写为: /exam/name 因为child本身就是默认轴,所以可以省略。再比如如下表达式:
/child::exam/child::city[attribute::state=”NC”]
attribute轴可以用@代替,那这样可以得缩写:/exam/city[@state=”NC”]
好,我们现在打开SQL SERVER 2005,新建一个查询测试如下代码来更好解释一下缩写的规律:
declare @xml xml
set @xml = '<address>
<exam>
<name> flystone1 </name>
<street> 1401 Main Street </street>
<city state="NC">Anytown</city>
<postal-code> 210000 </postal-code>
</exam>
<exam>
<name> flystone2 </name>
<street> 1402 Main Street </street>
<city state="NM">Anytown</city>
<postal-code> 210000 </postal-code>
</exam>
</address>
'
select @xml.query('/descendant-or-self::node()/exam/name')
select @xml.query('/descendant-or-self::node()/child::exam/child::name')
select @xml.query('//child::exam/name')
select @xml.query('//child::exam/child::name')
select @xml.query('//exam/name')
select @xml.query('/address/exam/name')
select @xml.query('//exam/name')
/*
-----------------------------------------------------
<name> flystone1 </name><name> flystone2 </name>
(1 行受影响)
*/
select @xml.query('//exam/city[attribute::state="NC"]')
select @xml.query('//exam/city[@state="NC"]')
/*
---------------------------------
<city state="NC">Anytown</city>
(1 行受影响)
*/
select @xml.query('//exam/name/.')
select @xml.query('/address/exam/name/self::node()')
/*
--------------------------------------------------------
<name> flystone1 </name><name> flystone2 </name>
(1 行受影响)
*/
select @xml.query('//exam/name/..')
select @xml.query('/address/exam/name/parent::node()')
/*
-----------------------------------------
<exam><name> f ...... Anytown</city><postal-code>
(1 行受影响)
*/
以上所有select查询我们可以发现好多有同一个结果 ,那么我们可以看出来如下规律:
1、child 缺少轴可以省略,如:
select @xml.query('//child::exam/name')
select @xml.query('//child::exam/child::name')
select @xml.query('//exam/name')
2、/descendant-or-self::node()/ 代表上下节点和后代,可以用// ,如:
select @xml.query('/descendant-or-self::node()/exam/name')
select @xml.query('//exam/name')
3、attribute轴可以缩写 @,如:
select @xml.query('//exam/city[attribute::state="NC"]')
select @xml.query('//exam/city[@state="NC"]')
4、self::node()节点本身可以缩写为”.” 。
select @xml.query('//exam/name/.')
select @xml.query('/address/exam/name/self::node()')
5、parent::node 父节点可以省略为”..”
select @xml.query('//exam/name/..')
select @xml.query('/address/exam/name/parent::node()')
好,以上我们对全文拼写的语法与缩写语法的一些探讨。下面说绝对与相对,其实绝对路径是以 //或/ 开头的的路径表达式,绝对路径开头的// 或 / 后接着的就是相对路,但是以/开始的绝对路径后可以不跟任何相对路径,这一点得注意。
declare @xml xml
set @xml = '<address>
<exam>
<name> flystone1 </name>
<street> 1401 Main Street </street>
<city state="NC">Anytown</city>
<postal-code> 210000 </postal-code>
</exam>
<exam>
<name> flystone2 </name>
<street> 1402 Main Street </street>
<city state="NM">Anytown</city>
<postal-code> 210000 </postal-code>
</exam>
</address>
'
select @xml.query('./address/exam/name')
select @xml.query('/address/exam/name/.')
select @xml.query('/address/exam/name')
select @xml.query('//exam/name/.')
select @xml.query('/.')
select @xml.query('/')
select @xml.query('.')
select @xml.query('address/exam/name')
select @xml.query('//../././.')
select @xml.query('//./././.')