DB2 SQL/XMLʹÓÃ
º¯Êý£º
XMLPARSE£º½âÎö×Ö·û»òÕß´ó¶ÔÏó¶þ½øÖÆÊý¾Ý£¬²úÉúXML
XMLSERIALIZE£º½«XMLֵת»»Îª×Ö·û»òÕß´ó¶ÔÏó¶þ½øÖÆÊý¾Ý
XMLVALIDATE£º¸ù¾ÝXML schemaУÑéXMLÖµµÄÓÐЧÐÔ
XMLEXISTS£º¼ì²âXQueryÊÇ·ñ·µ»Ø½á¹û£¨ÈçÓÉÒ»¸ö»ò¶à¸öÏîËù×é³ÉµÄÐòÁУ©
XMLQUERY£ºÖ´ÐÐÒ»¸öXQuery²¢ÒÔÒ»¸öÐòÁзµ»Ø½á¹û
XMLTABLE£ºÖ´ÐÐÒ»¸öXQuery£¬ÒÔ¹ØÏµÊý¾Ý±íÐÎʽ·µ»Ø½á¹û£¨Èç¹û¿ÉÄܵϰ£©
XMLCAST£ºXMLÀàÐÍת»»
XMLEXISTS£º
SELECT name from clients
WHERE xmlexists(
'$c/Client/Address[zip="95116"]'
passing clients.contact as "c"
)
˵Ã÷£º
ÔÚWHERE×Ó¾äÖе÷ÓÃXMLEXISTSº¯Êý£¬²ÎÊýÖ¸¶¨ÁËXML·¾¶±í´ïʽ£¬DB2¾Ý´Ëµ¼º½µ½XMLÎĵµÓʱàÔªËØ²¢ÇÒ²éÕÒÖµ 95116¡£ “$c/Client/Address”Ö¸¶¨ÁËDB2ÔÚXMLÎĵµÖж¨Î»ÓʱàÔªËØµÄ·¾¶¡£ÃÀÔª·ûºÅ($)ÓÃÀ´Ö¸¶¨±äÁ¿¡£passing clients.contact as "c"¶Ô±äÁ¿c½øÐж¨Òå¡£ÕâÀ“clients”ÊDZíÃû£¬“contact”ÊÇ XMLÁеÄÁÐÃû¡£»»ÑÔÖ®£¬XMLÎĵµ´«µÝ¸øÁ˱äÁ¿c¡£ DB2¼ìË÷“contact”ÁÐÖеÄXMLÊý¾Ý£¬´Ó¸ù½Ú“client”ÏòÏ£¬Í¨¹ý½Úµã“Address”µ½½Úµã“zip”£¬¾¹ýÄ¿±êÓÊ±àÆ¥Åä¼ì²éÅжϸÿͻ§ÊÇ·ñ¾ÓסÔÚÄ¿±êµØÇø¡£
ÔÚDB2 9.5ÖнøÐÐÁ˼ò»¯£º
SELECT name from clients
WHERE xmlexists(
'$CONTACT/Client/Address[zip="95116"]'
)
XMLQuery£º
SELECT xmlquery('$c/Client/email' passing contact as "c")
from clients
WHERE status = 'Gold'
ʹÓÃXQuery FLWOR±í´ïʽÖеÄfor£º
SELECT name, xmlquery(
‘for $e in $c/Client/email[1] return $e’
passing contact as “c”
)
from clients
WHERE status = ‘Gold’
SELECT xmlquery('for $e in $c/Client/email[1]/text()
return <p>{$e}</p>'
passing contact as "c")
from clients
WHERE status = 'Gold'
XQueryµÄFLWOR±í´ïʽ FLWORÊǼ¸¸ö´ÊÊ××ÖĸµÄËõд£º
FOR£º¶ÔÐòÁнøÐеü´ú
LET£º°ó¶¨±äÁ¿
WHERE£º¶¨Òå¹ýÂËÆ÷
ORDER£º½«¹ýÂ˽á¹ûÅÅÐò
RETURN£º·µ»Ø²éѯ½á¹û
ʵÀý£º
CREATE TABLE dept(deptID CHAR(8),deptdoc XML);
½«ÈçÏÂXMLÎĵµ²åÈëµ½deptdocÁУº
Ïà¹ØÎĵµ£º
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER proc [dbo].[pr_xls_to_tb]
@path varchar(200),--EXCEL·¾¶Ãû
@tbName varchar(30),--±íÃû
@stName varchar(30) --excelÖÐÒª¶ÁµÄSHEETÃû
as
declare @sql varchar(500),--×îºóÒªÖ´ÐеÄSQL
@stName_Real varchar(35),--ÕæÕýµÄSHEETÃû
......
ÒòΪÑöÍûORACLE£¬ËùÒÔÒ»Ö±¶¼ÒÔΪSQL SERVERºÜ±¿¡£
¾Ý´«SQL 2005ÓÐÁËRowIDµÄ¶«Î÷£¬¿ÉÒÔ½â¾öTOPÅÅÐòµÄÎÊÌâ¡£¿Éϧ»¹Ã»Óлú»áÌåÑé¡£ÔÚSQL 2000ÖÐд´æ´¢¹ý³Ì£¬×Ü»áÓöµ½ÐèÒªTOPµÄµØ·½£¬¶øÒ»µ©Óöµ½TOP£¬ÒòΪû°ì·¨°ÑTOPºóÃæµÄÊý×Ö×÷Ϊ±äÁ¿Ð´µ½Ô¤±àÒëµÄÓï¾äÖÐÈ¥£¬ËùÒÔÖ»Äܹ»Ê¹Óù¹Ôì SQL£¬Ê¹ÓÃExecÀ´Ö´ÐС£²»ËµÐ§ÂʵÄÎÊÌ⣠......
SQL×¢Èë¹¥»÷·À·¶¼¼ÇÉ
Ò»°ãµÄSQL×¢Èë¹¥»÷¶¼ÊÇͨ¹ý¹¹½¨Ò»Ìõ¸´ÔÓµÄsqlÓï¾ä£¬
ͨ¹ýÍøÒ³Â©¶´À´Ö´ÐÐsqlÓï¾ä£¬´ïµ½¹¥»÷Êý¾Ý¿âµÄÄ¿µÄ¡£
Èçͨ¹ýÎÄÕÂIDÀ´²éѯijһƪÎÄÕµÄÍøÒ³£¬
ͨ³£²ÉÓõÄsqlÓï¾äΪ£º
sql="select top 1 * from articles where articId="&request("id")
ÄÇô¿ÉÒÔ¼òµ ......
¶ÔÓÚ·þÎñÆ÷ÉÏÊý¾ÝµÄ±¸·Ý¹¤×÷ÊÇÍø¹ÜÔ±ÈÕ³£¹¤×÷ÖÐ×îÖØÒªµÄ¹¤×÷Ö®Ò»£¬SQLServerAgent·þÎñʹµÃÎÒÃÇ¿ÉÒÔÓÃSQLÉϵēÊý¾Ý¿âά»¤¼Æ»®”¹¦
ÄÜÀ´×Ô¶¯±¸·ÝÊý¾Ý£¬µ«×î½üÎÒ·¢ÏÖ·þÎñÆ÷ÉÏSQLServerAgent·þÎñÎÞ·¨Æô¶¯ÁË£¬»ØÏë×î½üËù×öµÄ²Ù×÷£¬ÔÀ´ÊÇÒòΪÎÒ°ÑSQLÖÐ
Builtin/AdministratorsÕâ¸öÕʺŵķþÎñÆ÷·ÃÎÊȨÏÞ½ûÖ¹ ......
Ò»¡¢×Ö·ûת»»º¯Êý
1¡¢ASCII()
·µ»Ø×Ö·û±í´ïʽ×î×ó¶Ë×Ö·ûµÄASCII ÂëÖµ¡£ÔÚASCII£¨£©º¯ÊýÖУ¬´¿Êý×ÖµÄ×Ö·û´®¿É²»ÓÑ’À¨ÆðÀ´£¬µ«º¬ÆäËü×Ö·ûµÄ×Ö·û´®±ØÐëÓÑ’À¨ÆðÀ´Ê¹Ó㬷ñÔò»á³ö´í¡£
2¡¢CHAR()
½«ASCII Âëת»»Îª×Ö·û¡£Èç¹ûûÓÐÊäÈë0 ~ 255 Ö®¼äµÄASCII ÂëÖµ£¬CHAR£¨£© ·µ»ØNULL ¡£
3¡¢LOWER()ºÍ ......