sql ²éѯXML - MS-SQL Server / ÒÉÄÑÎÊÌâ
ÓÐÒ»¸öXML
XML code:
<Person>
<Node>
<Name>admin</Name>
<Date>2010-4-6</Date>
</Node>
<Node>
<Name>lisi</Name>
<Date>2010-4-6</Date>
</Node>
<Node>
<Name>wangwu</Name>
<Date>2010-4-7</Date>
</Node>
</Person>
ʵÏÖ
Name:admin ListName:wangu
Ôõôдsql Óï¾ä лл¸÷λ´óϺ ¸ø¸öʵÓõÄÁ´½ÓÒ²¿ÉÒÔ
лл¸÷λ´óÏÀÁË£¡
SQLServer2005 XMLÔÚT£SQL²éѯÖеĵäÐÍÓ¦ÓÃ
http://topic.csdn.net/u/20081107/17/68aaf5a9-c596-4ab5-ae18-f3370b2ab35b.html
SQL code:
declare @xml xml
set @xml='
<Person>
<Node>
<Name>admin</Name>
<Date>2010-4-6</Date>
</Node>
<Node>
<Name>lisi</Name>
<Date>2010-4-6</Date>
</Node>
<Node>
<Name>wangwu</Name>
<Date>2010-4-7</Date>
</Node>
</Person>'
SELECT *
from(
SELECT
A.x.query('Name').value('.','varchar(10)') Name ,
A.x.query('Date').value('.','datetime') Date
from @xml.nodes('/Person/Node') AS A(x))a
/*
Name Date
---------- ---
Ïà¹ØÎÊ´ð£º
ÎÒÓÐÒ»¸ö±í£¬½á¹¹ÊÇÕâÑù¡£
ת³ö µ¥Î» תÈ뵥λ ±ÊÊý ½ð¶î
date(Ö÷) outid(Ö÷) inid(Ö÷) num amt
2009 1 2 1 500 Ϊ 1 µ¥Î» ÔÚ2009Ä ......
´ÓÊý¾Ý¿âÖвéѯһÕűíµÄÊý¾Ý
select ²¿ÃÅ,ÐÕÃû from tb
ÈçºÎ²ÅÄÜÉú³ÉÏÂÃæµÄxml¸ñʽ
XML code:
<folder state="unchecked" label="È«²¿">
¡¡¡¡ <folder state="unchecked&qu ......
´ÓÊý¾Ý¿âÖвéѯһÕűíµÄÊý¾Ý
select ²¿ÃÅ,ÐÕÃû from tb
ÈçºÎ²ÅÄÜÉú³ÉÏÂÃæµÄxml¸ñʽÎļþ
<folder state="unchecked" label="È«²¿">
¡¡¡¡<folder state="unchecked" isBra ......