首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

sqlserver2005读取XML资料

2012-08-10 
sqlserver2005读取XML文件?xml version1.0 encodinggb2312 ?- BTSINFOVERSION READONLYtrue

sqlserver2005读取XML文件
<?xml version="1.0" encoding="gb2312" ?> 
- <BTSINFO>
  <VERSION READONLY="true">0820040500000000</VERSION> 
- <EDIT_AREA>
- <BTS SYSTEM="2" ALIAS="SY110三亚假日酒店">
- <CELL_1X>
  <CELL CELLID_READONLY="0" PHYCELLID_READONLY="0" CI="31101" PILOT_PN="45" REG_ZONE="3" LAC="3" MSCOFFICEID="255" /> 
  <CELL CELLID_READONLY="1" PHYCELLID_READONLY="1" CI="31102" PILOT_PN="213" REG_ZONE="3" LAC="3" MSCOFFICEID="255" /> 
  </CELL_1X>
- <CELL_DO>
  <CELL CELLID_READONLY="0" PHYCELLID_READONLY="0" PILOT_PN="45" SUBNETINDEXID="" SECTORID="00-00-00-00-00-00-00-00-00-00-00-00-00-00-00-00" /> 
  <CELL CELLID_READONLY="1" PHYCELLID_READONLY="1" PILOT_PN="213" SUBNETINDEXID="" SECTORID="00-00-00-00-00-00-00-00-00-00-00-00-00-00-00-00" /> 
  </CELL_DO>
  </BTS>
  </EDIT_AREA>
- <READONLY_AREA>
- <ASSISTANT_AREA>
  <COMMON BTSTYPE="0" BTSVERSION="20" ISINCLUDEDOCELL="true" CELLNUM_DO="2" ISINCLUDEDOCHM="true" CELLNUM_1X="2" FWDPOWER1X="true" BTSPARAMETER="true" CELLNUM="2" /> 
- <CARRIERLIST FREQNUMBER="3" CARRIERIDS="0,1,5" CARRIERNUM_1X="4" CARRIERNUM_DO="2">
  <CARRIER CARRIERID="0" CDMA_FREQ="283" BAND_CLASS="0" /> 
  <CARRIER CARRIERID="1" CDMA_FREQ="201" BAND_CLASS="0" /> 
  <CARRIER CARRIERID="5" CDMA_FREQ="37" BAND_CLASS="0" /> 
  </CARRIERLIST>
  </ASSISTANT_AREA>
- <SQLSCRIPT>
  <SQL>update b_sysfunction set flag = 0,starttime = getdate()</SQL> 
- <!-- B_SYSTEM
  --> 
- <!-- DEVID_B,SYSTEM,BTSTYPE,BTSVERSION,LOCATIONNAME,USERdEFINEDsTATE,SWVERSION,ALIAS_B,UCFSNCHECK,ABISMODE,SERIALNUMBER,RESERVED1,RESERVED2,RESERVED3,TESTE1WIDTH,POWERTYPE,ENABLECLOSEFWDRIM,CLOSEFWDRIM,OPENFWDRIM,CFRTIMER,BSMODE,ENABLEDISDELAY,UPSINFO,WORKTIME_START,WORKTIME_END,isAllowAllShutdown,SRACKMODE,crcmode,ABISALLOCRULE,MultiPlexing,province,CITY,county,address
  --> 
  <SQL>insert into B_SYSTEM (DEVID_B,SYSTEM,BTSTYPE,BTSVERSION,LOCATIONNAME,USERdEFINEDsTATE,SWVERSION,ALIAS_B,UCFSNCHECK,ABISMODE,SERIALNUMBER,RESERVED1,RESERVED2,RESERVED3,TESTE1WIDTH,POWERTYPE,ENABLECLOSEFWDRIM,CLOSEFWDRIM,OPENFWDRIM,CFRTIMER,BSMODE,ENABLEDISDELAY,UPSINFO,WORKTIME_START,WORKTIME_END,isAllowAllShutdown,SRACKMODE,crcmode,ABISALLOCRULE,MultiPlexing,province,CITY,county,address) values (#DEVID_B#,#SYSTEM#,0,20,NULL,NULL,NULL,'#BTS_ALIAS_B#',1,0x01,'',NULL,NULL,NULL,0,0,0,7,66,60,0,0,0,0,0,0,0,0,NULL,0,NULL,NULL,NULL,NULL)</SQL> 
- <!-- B_SENSORCFG
  --> 
- <!-- devid_b,system,sensorid,highlimit,lowlimit,isconfigure
  --> 
  <SQL>insert into B_SENSORCFG (devid_b,system,sensorid,highlimit,lowlimit,isconfigure) values (#DEVID_B#,#SYSTEM#,2,40,5,1)</SQL> 
  <SQL>insert into B_SENSORCFG (devid_b,system,sensorid,highlimit,lowlimit,isconfigure) values (#DEVID_B#,#SYSTEM#,3,85,2,0)</SQL> 
  <SQL>insert into B_SENSORCFG (devid_b,system,sensorid,highlimit,lowlimit,isconfigure) values (#DEVID_B#,#SYSTEM#,4,-500,-500,0)</SQL> 
  <SQL>insert into B_SENSORCFG (devid_b,system,sensorid,highlimit,lowlimit,isconfigure) values (#DEVID_B#,#SYSTEM#,7,-500,-500,1)</SQL> 


  <SQL>insert into B_SENSORCFG (devid_b,system,sensorid,highlimit,lowlimit,isconfigure) values (#DEVID_B#,#SYSTEM#,8,-500,-500,0)</SQL> 
  <SQL>insert into B_SENSORCFG (devid_b,system,sensorid,highlimit,lowlimit,isconfigure) values (#DEVID_B#,#SYSTEM#,13,50,10,0)</SQL> 
  <SQL>insert into B_SENSORCFG (devid_b,system,sensorid,highlimit,lowlimit,isconfigure) values (#DEVID_B#,#SYSTEM#,14,50,10,0)</SQL> 
  <SQL>insert into B_SENSORCFG (devid_b,system,sensorid,highlimit,lowlimit,isconfigure) values (#DEVID_B#,#SYSTEM#,30,-500,-500,0)</SQL> 
  <SQL>insert into B_SENSORCFG (devid_b,system,sensorid,highlimit,lowlimit,isconfigure) values (#DEVID_B#,#SYSTEM#,31,-500,-500,1)</SQL> 
  <SQL>insert into B_SENSORCFG (devid_b,system,sensorid,highlimit,lowlimit,isconfigure) values (#DEVID_B#,#SYSTEM#,32,50,10,1)</SQL> 
  <SQL>insert into B_SENSORCFG (devid_b,system,sensorid,highlimit,lowlimit,isconfigure) values (#DEVID_B#,#SYSTEM#,33,50,10,0)</SQL> 
  <SQL>insert into B_SENSORCFG (devid_b,system,sensorid,highlimit,lowlimit,isconfigure) values (#DEVID_B#,#SYSTEM#,34,50,10,0)</SQL> 
  <SQL>insert into B_SENSORCFG (devid_b,system,sensorid,highlimit,lowlimit,isconfigure) values (#DEVID_B#,#SYSTEM#,35,-500,-500,0)</SQL> 
  <SQL>insert into B_SENSORCFG (devid_b,system,sensorid,highlimit,lowlimit,isconfigure) values (#DEVID_B#,#SYSTEM#,36,50,10,1)</SQL> 
  <SQL>insert into B_SENSORCFG (devid_b,system,sensorid,highlimit,lowlimit,isconfigure) values (#DEVID_B#,#SYSTEM#,51,40,5,0)</SQL> 
  <SQL>insert into B_SENSORCFG (devid_b,system,sensorid,highlimit,lowlimit,isconfigure) values (#DEVID_B#,#SYSTEM#,52,-500,-500,0)</SQL> 
  <SQL>insert into B_SENSORCFG (devid_b,system,sensorid,highlimit,lowlimit,isconfigure) values (#DEVID_B#,#SYSTEM#,63,285,165,1)</SQL> 
  <SQL>insert into B_SENSORCFG (devid_b,system,sensorid,highlimit,lowlimit,isconfigure) values (#DEVID_B#,#SYSTEM#,64,58,48,1)</SQL> 
  <SQL>insert into B_SENSORCFG (devid_b,system,sensorid,highlimit,lowlimit,isconfigure) values (#DEVID_B#,#SYSTEM#,65,58,47,1)</SQL> 
  <SQL>insert into B_SENSORCFG (devid_b,system,sensorid,highlimit,lowlimit,isconfigure) values (#DEVID_B#,#SYSTEM#,66,40,0,1)</SQL> 
  <SQL>insert into B_SENSORCFG (devid_b,system,sensorid,highlimit,lowlimit,isconfigure) values (#DEVID_B#,#SYSTEM#,67,60,-10,1)</SQL> 
  <SQL>insert into B_SENSORCFG (devid_b,system,sensorid,highlimit,lowlimit,isconfigure) values (#DEVID_B#,#SYSTEM#,68,50,0,1)</SQL> 
  <SQL>insert into B_SENSORCFG (devid_b,system,sensorid,highlimit,lowlimit,isconfigure) values (#DEVID_B#,#SYSTEM#,69,50,0,1)</SQL> 
  <SQL>insert into B_SENSORCFG (devid_b,system,sensorid,highlimit,lowlimit,isconfigure) values (#DEVID_B#,#SYSTEM#,70,20,0,1)</SQL> 

请问高手如何将提取<SQL>之间的语句，并将这些数据读取到SQL数据库中，谢谢

[解决办法]

SQL code
SQL2005里一项令人心动的内容就是支持XML类型了,而且今天刚刚知道,原来本身还支持在SQL2005的语句里解析XML. 不多说，直接贴例子代码: 访问一个结点: SELECT     TOP (10)                       dataxml.query('declare namespace AWMI="http://webservices.test.com/ABCDCommerceService/2005-10-05" ; data(/AWMI:ItemLookupResponse/AWMI:OperationRequest/AWMI:RequestId)')                        AS Expr1 FROM         asin 访问一个结点的属性: SELECT     TOP (10)                       dataxml.query('declare namespace AWMI="http://webservices.test.com/ABCDCommerceService/2005-10-05" ; data(/AWMI:ItemLookupResponse/AWMI:OperationRequest/AWMI:RequestId/@approved)')                        AS Expr1 FROM         asin dataxml是我表中的字段名称,也就是类型为XML的字段. 先声明了一个命名空间,然后直接按照结点访问,就这么简单.至于属性就加个@就可以了 我们还可以给这个XML设置验证格式,当然,可以用XSD验证. 在写下面的语句: CREATE XML SCHEMA COLLECTION [dbo].[requestSchemaCollection] AS N'这里是XSD的内容' 注意编码在SQL里默认是UTF16的,你可以把编码那句删除. 创建成功以后就可以在programmablitity---types---XML schema collections里看到你创建的验证格式了.去修改你的表,把格式添加进table的XML列里.
读书人精选
热点排行