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)¡£ÎÞ·¨½â¾ö ......
Select CONVERT(varchar(100), GETDATE(), 0) as 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 1) as 05/16/06
Select CONVERT(varchar(100), GETDATE(), 2) as 06.05.16
Select CONVERT(varchar(100), GETDATE(), 3) as 16/05/06
Select CONVERT(varchar(100), GETDATE(), 4) as 16.05.06
Select CON ......
XML SchemaÖ÷ÒªÓÃÀ´Éè¼ÆXMLÎĵµµÄ½á¹¹£¬×¢Òâ²»ÊǸñʽ¡£ÓÐÈËÎÊÔõô²»ÊǸñʽ£¿¸ñʽ¸ú½á¹¹Ê²Ã´Çø±ð£¿ÎÒÏë¸ã¿ª·¢µÄÈ˶¼¶Ô´úÂë½øÐиñʽ»¯¹ý£¬Ò²¾ÍÊÇËõ½ø¡¢×ÖÌåµÈµÈ£»¶øÎÒÃÇдµÄ³ÌÐòʵ¼ÊÉÏÒ²ÊǶ¨ÒåÒ»Öֽṹ£¬Ëü²»»áÒòΪÄãÊÇ·ñËõ½ø¡¢²ÉÓò»Í¬µÄ×ÖÌå¾ÍÓÐËù¸Ä±ä°É£¿ºÜ¶àÈËÄÄÁ˲»·ÖÐеÄxmlÎĵµËµÓÃVSµÄ¹¤¾ß¸ ......
/*
* Ö÷Òª×÷ÓÃ;
* ´Óxml¶ÁÈ¡ÓÎÏ·ÅäÖÃÐÅÏ¢»ò±£´æ
*/
using System;
using System.Collections.Generic;
using System.Text;
using System.Windows;
using System.Windows.Forms;
using System.IO;
using System.Xml;
using System.Reflection;
namespace Game
{
class Config
{
Ke ......
http://blog.csdn.net/java2000_net/archive/2008/04/05/2252640.aspx
http://sqlserver.chinahtml.com/2006/SQL-mssql11432786154012.shtml
http://www.cnblogs.com/garnai/archive/2007/09/19/898221.html
http://tech.ccidnet.com/art/1099/20050223/214511_1.html
http://www.wangchao.net.cn/bbsdetail_43009.html ......