SQL2005XML存储过程应用

《SQL2005XML存储过程应用》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、SQL2005XML存储过程应用1OPENXML(memoryintensive)OPENXMLisaSQLServerfunction,whichacceptsastreamofXMLdataandprovidesanin-memoryrelationalrowsetviewoftheXMLdata.--参数示例类型可以是XML或者字符类型，如VARCHAR(8000),VARCHAR(MAX)等DECLARE@aXMLSET@a='

2、="10.00"SaleDate="10/11/2006"SaleBatchID="4523"CustomerID="2398"/>'--存储过程示例CREATEPROCEDUREdbo.usp_InsertShoppingCartOrder(@xmlXML)ASBEGINSETNOCOUNTONDEC

3、LARE@PointerINTEXECUTEsp_xml_preparedocument@PointerOUTPUT,@xmlINSERTINTOWebSales(ProductID,SalePrice,SaleDate,SaleBatchID,CustomerID)SELECTProductID,Price,SaleDate,SaleBatchID,CustomerIDFROMOPENXML(@Pointer,'/ShoppingCart/Purchase')WITH(ProductIDINT,PriceMONEY

4、,SaleDateSMALLDATETIME,SaleBatchIDINT,CustomerIDINT)EXECsp_xml_removedocument@PointerSETNOCOUNTOFFENDWethenmustcallthesystemstoredproceduresp_xml_preparedocument,whichcreatesanin-memoryrepresentationofthexmldocument,whichitalsoacceptsasaparameter.Oncewehavethem

5、emorypointertotheXMLvariable,wecancallOPENXML,whichyoumaydowithseveraldifferentparametersthatgiveyouverygranularcontrolovertheresultsetoftheXMLdata.WeareprovidingthepointertotheXMLdocument,alongwithanXPATHquerythatidentifiesthenodesintheXMLdatawewishtoreturn.Th

6、eWITHclauseoftheOPENXMLfunctionallowsyoutospecifytherowsetformatthatyouwishtobereturned.OnceweinsertintoourWebSalestable,wecallthesp_xml_removedocumentsystemstoredproceduretocleanourXMLdatafromSQLServer'smemory.或者可以把输入的XML数据插入不同的表OPENXML的缺点：OPENXMLisverymemoryi

7、ntensive.Thepointerreturnedbythesystemstoredproceduresp_xml_preparedocumentisamemorypointertoaCOMXMLdocumentobjectmodel.So,youmustbecarefulnottoloadlargeXMLdocumentsintomemorywithOPENXMLbecauseitmayoverloadyourserver'smemory.2.XQuery(可解决OPENXML的缺点)WhatisXQuery?

8、XQuery,alsoknownasXMLQuery,isalanguagedesignedtoqueryXMLdata,allowingyoutoextractnodesandelementsasneeded.ItisdefinedbytheW3C,andisavailableforuseintoday'smostpopulardatabas