SQL XML DELETE
--A. ´Ó´æ´¢ÔÚ·ÇÀàÐÍ»¯µÄ xml ±äÁ¿ÖеÄÎĵµÖÐɾ³ý½Úµã
DECLARE @myDoc xml
SET @myDoc = '<?Instructions for=TheWC.exe ?>
<Root>
<!-- instructions for the 1st work center -->
<Location LocationID="10" LaborHours="1.1" MachineHours=".2" >
Some text 1
<step>Manufacturing step 1 at this work center</step>
<step>Manufacturing step 2 at this work center</step>
</Location>
</Root>'
SELECT @myDoc
-- delete an attribute
SET @myDoc.modify('
delete /Root/Location/@MachineHours
')
SELECT @myDoc
-- delete an element
SET @myDoc.modify('
delete /Root/Location/step[2]
')
SELECT @myDoc
-- delete text node (in <Location>
SET @myDoc.modify('
delete /Root/Location/text()
')
SELECT @myDoc
-- delete all processing instructions
SET @myDoc.modify('
delete //processing-instruction()
')
SELECT @myDoc
--B. ´Ó´æ´¢ÔÚ·ÇÀàÐÍ»¯µÄ xml ÁÐÖеÄÎĵµÖÐɾ³ý½Úµã
delete XML DML Óï¾ä´Ó´æ´¢ÔÚÁÐÖеÄÎĵµÖÐɾ³ý <Features> µÄµÚ¶þ¸ö×ÓÔªËØ¡£
CREATE TABLE #T (i int, x xml)
go
INSERT INTO #T VALUES(1,'<Root>
<ProductDescription ProductID="1" ProductName="Road Bike">
<Features>
<Warranty>1 year parts and labor</Warranty>
<Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
</Features>
</ProductDescription>
</Root>')
go
-- verify the contents before delete£¬query·µ»ØFeaturesÔªËØÏµÄËùÓнڵã,ÒÔXML¸ñʽ·µ»Ø
SELECT x.query('//ProductDescription/Features')
from #T
-- delete the second feature
UPDATE #T
SET x.modify('delete /Root/ProductDescription/Features/*[2]')
-- verify the deletion
SELECT x.query(' //ProductDescription/Features')
from #T
DROP TABLE #T
C. ´Ó·ÇÀàÐÍ»¯µÄ xml ÁÐÖÐɾ³ý½Úµã
create table #T(ProductModelID int primary key,
Instructions xml )
go
insert #T
select ProductModelID, Instructions
from Prod
Ïà¹ØÎĵµ£º
ÏÖÏó£º
ÔÚʹÓÃMicrosoft SQL Server 2005ʱ£¬Òª´´½¨Ò»¸öµÇ¼Ãû£¬²¢Îª¸ÃµÇ¼Ãû¹ØÁªÁËÒ»¸öÊý¾Ý¿â£¬µ«ÊÇÔÚÑ¡Ôñ“°²È«¶ÔÏó”Ñ¡Ïîʱ£¬È´³öÏÖÁËÈçÌâËùʾµÄ´íÎ󡣯äËûÐÅÏ¢ÏÔʾΪ£ºÖ´ÐÐTransact-SQLÓï¾ä»òÅú´¦Àíʱ·¢ÉúÁËÒì³£(Microsoft.SqlServer.ConnectionInfo)¡£ÎÞ·¨½â¾ö ......
ΪÁË´¦Àí SQL Óï¾ä£¬ORACLE ±ØÐë·ÖÅäһƬ½ÐÉÏÏÂÎÄ( context area )µÄÇøÓòÀ´´¦ÀíËù±ØÐèµÄÐÅÏ¢£¬ÆäÖаüÀ¨Òª´¦ÀíµÄÐеÄÊýÄ¿£¬Ò»¸öÖ¸ÏòÓï¾ä±»·ÖÎöÒÔºóµÄ±íʾÐÎʽµÄÖ¸ÕëÒÔ¼°²éѯµÄ»î¶¯¼¯(active set)¡£
ÓαêÊÇÒ»¸öÖ¸ÏòÉÏÏÂÎĵľä±ú( handle)»òÖ¸Õ롣ͨ¹ýÓα꣬PL/SQL¿ÉÒÔ¿ØÖÆÉÏÏÂÎÄÇøºÍ´¦ÀíÓï¾äʱÉÏÏÂÎÄÇø»á·¢ÉúЩʲôÊÂÇ ......
×î½üÔÚȺÀï´ó¼ÒͻȻÌÖÂÛÏÂfb(firebird)À´ÁË£¬ºÇ£¬¿´ÁËһЩ×ÊÁÏ£¬Í»È»·¢¾õÕâ¸ö¶«Î÷Ò²ÂùºÃÍæµÄ£¬ÓÚÊÇ¿ªÊ¼´óÁ¿µÄ²éÕÒÓйصÄ×ÊÁÏ£¬ÏÂÃæÊÇÒ»
ЩfbϵÄsqlд·¨£¬×ª×ÔÍøÉÏ£¬¸÷λÅóÓѽ«¾ÍЩ¿´¿´°É£¬¹ý¼¸ÌìżÕûÀíºÃ.netÏÂʹÓÃfbµÄdemoÉÏÀ´¸ø´ó¼Ò¿´¿´°É£¬µ½Ê±Ö§³ÖÖ§³Ö°É£¬ºÇºÇ
Ò»¡¢·Öҳд·¨Ð¡Àý£º
SELECT FIRST
10 templa ......
--´¦ÀíʾÀý
--ʾÀýÊý¾Ý
create table tb(ID int,Name varchar(10),ParentID int)
insert tb select 1,'AAAA' ,0
union all select 2,'BBBB' ,0
union all select 3,'CCCC' ,0
union all select 4,'AAAA-1' ,1
union all select 5,'AAAA-2' ,1
u ......