18¾ä»°ÈëÃÅSQLServer XML
/*
sql xml ÈëÃÅ:
--by jinjazz
--http://blog.csdn.net/jinjazz
1¡¢xml: ÄÜÈÏʶԪËØ¡¢ÊôÐÔºÍÖµ
2¡¢xpath: Ñ°Ö·ÓïÑÔ£¬ÀàËÆwindowsĿ¼µÄ²éÕÒ(ûÓùýdirÃüÁîµÄ»°¾ÍÈ¥Ãæ±Ú)
Óï·¨¸ñʽ£¬ÕâЩÓï·¨¿ÉÒÔ×éºÏΪÌõ¼þ£º
"."±íʾ×Ô¼º£¬".."±íʾ¸¸Ç×£¬"/"±íʾ¶ù×Ó£¬"//"±íʾºó´ú£¬
"name"±íʾ°´Ãû×Ö²éÕÒ£¬"@name"±íʾ°´ÊôÐÔ²éÕÒ
"¼¯ºÏ[Ìõ¼þ]" ±íʾ¸ù¾ÝÌõ¼þÈ¡¼¯ºÏµÄ×Ó¼¯£¬Ìõ¼þ¿ÉÒÔÊÇ
Êý Öµ£ºÊý×Ö,last(),last()-Êý×Ö µÈ
²¼¶ûÖµ£ºposition()<Êý×Ö,@name='Ìõ¼þ',name='Ìõ¼þ'
Ìõ¼þÊDz¼¶ûÖµµÄʱºò¿ÉÒԺϲ¢¼ÆË㣺and or
3¡¢xquery: »ùÓÚxpath±êµÄ×¼²éѯÓïÑÔ£¬sqlserver xquery°üº¬ÈçϺ¯Êý
exist(xpathÌõ¼þ)£º·µ»Ø²¼¶ûÖµ±íʾ½ÚµãÊÇ·ñ´æÔÚ
query(xpathÌõ¼þ)£º·µ»ØÓÉ·ûºÏÌõ¼þµÄ½Úµã×é³ÉµÄеÄxmlÎĵµ
&
Ïà¹ØÎĵµ£º
SQLSERVERµÄʼþ̽²âÆ÷ÊÇÒ»¸öºÜÇ¿´óºÜÖ±¹ÛµÄ¹¤¾ß£¬µ±ÎÒÃÇÔÚERPϵͳÀïÃæ×öÒ»¶¨²Ù×÷µÄʱºò£¬¶¼»áÔÚÊý¾Ý¿âÀïÃæÐγɲéѯ»òÕßÔöɾ¸ÄµÄT-SQLÓï¾ä¡£Í¨¹ýʼþ̽²âÆ÷¾Í¿ÉÒÔÖªµÀERPÀïÃæµÄµ¥¾ÝÔÚÊý¾Ý¿âµÄÄĸö±íÀïÃ棬ÉõÖÁÄĸö×Ö¶ÎÀïÃæ¡£»¹¿ÉÒÔ¸ù¾Ý̽²âµ½µÄSQLÓï¾ä·ÖÎöERPϵͳµÄ±í½á¹¹£¬±íÖ®¼äµÄ¹ØÁª×Ö¶ÎÊÇÄĸö¡£
´ó¼Ò³õ´ò¿ªÊ¼þ̽²âÆ÷ ......
select
convert(char(4),auth,120)+'Äê'+
substring(convert(char(10),auth,120),6,2)+'ÔÂ'+
substring(convert(char(10),auth,120),9,2)+'ÈÕ',
convert(char(4),appr,120)+'Äê'+
substring(convert(char(10),appr,120),6,2)+'ÔÂ'+
substring(convert(char(10),appr,120),9,2)+'ÈÕ'
from a
ÒÔÉÏ´úÂëʵÏֵĹ¦Ä ......
//test.java
import java.io.BufferedOutputStream;
import java.io.DataOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
imp ......
SQL Server 2005Êý¾Ý¿â±¸·ÝºÍ»Ö¸´ÌØÐÔ
SQL Server 2000ÖдæÔÚµÄÐí¶àµÄ±¸·ÝºÍ»Ö¸´ÌØÐÔ¶¼Í¬Ñù±£ÁôÔÚÁËSQL Server 2005ÖУ¬µ«ÊÇÓÐһЩеÄÌá¸ßͬÑùÖµµÃÎÒÃǹØ×¢¡£
¾µÏñ±¸·Ý
SQL Server 2005ÈÃÄã¿ÉÒÔ´´½¨¾µÏñ±¸·Ý¡£¾µÏñ±¸·ÝÔÊÐíÄãΪ±¸·ÝÎļþ´´½¨Á½¸ö»òÕßËĸöͬÑùµÄ¿½±´£¬ÒÔ·À±¸ÆäÖеÄijһ¸ö¼¯ºÏË𻵵ÄÇé¿ö¡£¾µÏñ¾ßÓ ......
¿ÉÒÔÓÃÀ©Õ¹´æ´¢¹ý³Ìxp_dirtree»ñÈ¡ÎļþÁÐ±í£¬ÓÃopenrowsetµ¹ÈëÊý¾Ýµ½¶þ½øÖÆ×ֶΡ£
openrowsetµÄÓ÷¨¿ÉÒԲο¼msdn
http://technet.microsoft.com/zh-cn/library/ms190312.aspx
Èç¹ûÎļþºÜ¶à£¬½¨Ò黹ÊÇÓóÌÐòµ¹ÈëÁË
if (object_id ('t_bulkResult' ) is not null )
drop table t_bulkResult ......