SQL Server XQuery and XML.docx
- 文档编号:7406973
- 上传时间:2023-05-11
- 格式:DOCX
- 页数:60
- 大小:498.37KB
SQL Server XQuery and XML.docx
《SQL Server XQuery and XML.docx》由会员分享,可在线阅读,更多相关《SQL Server XQuery and XML.docx(60页珍藏版)》请在冰点文库上搜索。
SQLServerXQueryandXML
SQLServer2005XQueryandXML-DML
"AnOverviewofXMLSupportinSQLServer2005"
Eversincethemid90’s,asXMLhasevolvedtobecometheoptimumwaytoshare,transportandpersistdata,developershavesoughtefficientwaystostore,manipulateandgenerallytakeadvantageofitscapabilities.Increasinglyfastandeasy-to-useXMLparsershavebeendeveloped,transportprotocolssuchasSOAPhavebeenusedtoimplementWebServicesaroundXML,andmanyapplications,toolsandprogramminglibrariesnowincludefeaturestoimportandexporttheirdataasXML.
However,theoneareathathasseenlessdevelopment,particularlyintheMicrosoftworld,istheimplementationofefficientandrobusttechnologiesforpersistingXML.XMLisbasicallyjusttext,andthemostcommonpersistenceformatisasadiskfileinsomestandardtext-basedformatsuchasANSI,Unicode,etc.Thisisgenerallyfineforsingle-userapplications,butdoespromptsomeseriousquestionswhenusedinaserver-basedand/ormulti-userenvironment.
ThisandthetwosubsequentarticleslookathowthelatestversionofMicrosoft’senterprise-leveldatabase,SQLServer2005,nowoffersgreatsupportforandcloseintegrationwithXMLasadatapersistenceformat.Thisincludesnewwaystovalidate,storeandqueryXMLdocumentsthatarestoredwithinthedatabase.SQLServer2005providesnativesupportforXMLthatcanvastlyimproveapplicationperformance,whilesupportingrobustandsafemulti-useraccesstothedatacontainedwithintheXMLdocuments.
Thetopicswe’llbecoveringinthisarticleare:
∙AbriefoverviewofthewaythatSQLServer2005storesXMLdocumentsandschemas
∙HowSQLServer2005providessupportforqueryingandmanipulatingXMLdocuments
∙AsimpletestapplicationthatallowsyoutoexperimentwithXQuery
Intwosubsequentarticles,we'llseesometechniquesforimprovingtheperformanceofapplicationsthatworkwithXMLdocuments,aswellassomeexamplesofthedifferentwaysyoucanuseXQuery:
∙Extractingdatafromxmlcolumns,usingparameterwithXQueryandcombiningXQueryandXSL-T
∙Updatingthecontentsofxmlcolumns,andusingXQueryinamanagedcodestoredprocedure
AnOverviewofXMLSupportinSQLServer2005
SQLServer2005addsaraftofnewfeaturestosupportXMLdatastorageandmanipulation.ThesefeaturesmakeiteasiertopersistyourXMLdocumentswithinthedatabase,whileprovidingincreasedperformanceoverthetraditionaltechniques.We’llbecomparingthesetraditionaltechniques,andseeinghowyoucanimprovetheperformanceofyourapplications,inParts2and3.Forthemoment,however,we’llbrieflyexplorethenewfeaturesinSQLServer2005.Theseinclude:
∙AdedicateddatatypenamedxmlthatcanbeusedtostoreXMLdocumentsorfragmentsofXML
∙TheabilitytoregisterXMLschemaswithSQLServer2005,andstoreschemainformationwithinthedatabase
∙AutomaticvalidationofXMLdocumentswhenaschemaispresent;andautomaticshreddingoftheXMLdatatosupportefficientqueryingandupdatingofthecontent
∙AnimplementationofasubsetoftheW3CXQuerylanguageandXML-DMLtoprovidethisqueryingandupdatefacility
∙Supportforhostingthe.NETCommonLanguageRuntime(CLR)withinSQLServer,whichallowsstoredproceduresthatmanipulateXMLdocumentstobewritteninmanagedcode
You’llseehowallthesefeaturescomeintoplaythroughoutthesethreearticles,andhowtheyopenupnewtechniquesforworkingwithXMLdocumentsandXMLdata.
XMLSchemasandtheW3CInfosetModel
Inrecentyears,it’sbecomeincreasinglyobviousthatthemajorusesofXMLareasawayofstoringbothrowset(singletable)andhierarchical(multiple-table)data,ratherthanunstructuredinformationsuchasnewspaperarticles.Forexample,acommonuseofWebServicesin.NETapplicationsistoexposedatathatrepresentsaDataSetinaformatthatallowsdiscoveryandtransmissionacrossHTTPnetworkssuchastheInternet.TheDataSetmaycontainasingletable,ormultipletablesthatarerelatedthroughprimaryandforeignkeys,andtheXMLdatacanbeusedtocompletelyreconstructthatDataSetontheclient.
TospecifythedatatypeforanelementoranattributeinanXMLdocumentyouuseaschema.Thisindicates,forexample,whetheravaluesuchas"42"(whichisstoredasatextstringwithintheXML)representseitheracharacterstringoranumericvalue.TheclientcanthenreconstructthedatastoredintheXMLdocumentsothatitisaccessibleastheappropriatedatatypes.ThisisattheheartoftherecentmovestowardstheXMLInformationSet(Infoset)model,whicheffectivelyconsidersanXMLdocumentasoneormoretypedrowsets.
FordetailsoftheW3CInfosetrecommendation,seehttp:
//www.w3.org/TR/xml-infoset/
ThismeansthatyoumustexposeanXMLSchema(ortherelevantschemainformation)foreveryXMLdocumentorfragmentinordertotakeadvantageoftheInfosetmodelanddata-typingoftheXMLcontent.SQLServer2005makesthiseasybyprovidingaschemarepositorythatyoucanusetostoreXMLschemas,anditwillautomaticallyusetheappropriateschematovalidateandstoreXMLdata.
TheXMLSchemaRepository
SchemasareaddedtoadatabasebyexecutingtheCREATEXMLSCHEMACOLLECTIONstatement,forexample:
CREATEXMLSCHEMACOLLECTIONMyNewSchemaCol' schemaxmlns="..."> ...schemacontent...
schema>'
Youcanaddmultipleschemasinonegobyconcatenatingthemtogether,usetheALTERXMLSCHEMACOLLECTIONstatementtoaddorremoveindividualschemasinacollection,andremovethecollectionusingtheDROPXMLSCHEMACOLLECTIONstatement.SeetheSQLServerhelpfilesformoredetails.
Thenameyouassigntothecollection("MyNewSchemaCol"inthecodeabove)isusedintheALTERandDROPstatements,andisdisplayedinSQLServerManagementStudio.However,youshouldincludeatargetNamespaceattributeintheopening
schemaxmlns: xs="http: //www.w3.org/2001/XMLSchema" targetNamespace="http: //myns/mydemoschema"> ... ThenyoulinkyourXMLdocumentstotheappropriateschemabyspecifyingthisnamespace: xmlversion="1.0"encoding="utf-8"? > //myns/mydemoschema"> ... YoucanuseSQLServerManagementStudiotoviewandmanageschemasandschemacollections.Forexample,Figure1showstheschemacollectionsintheAdventureWorkssampledatabasethatyoucandownloadandinstallinSQLServer2005. Figure1-TheschemacollectionsintheAdventureWorkssampledatabase TheNewxmlNativeDataType SQLServer2005supportsanewnativedatatypenamedxmlthatyouuseinexactlythesamewayasanyotherbuilt-indatatype.Youcanuseittodefineacolumntypeforatable,asaparameterorvariableinastoredprocedure,andanywhereelseyouwouldusebuilt-intypessuchasnvarchar,int,etc.ThexmltypecanstoreeitheracompleteXMLdocument,orafragmentofXML,aslongasitiswell-formed(youcannotuseanxmltypetostoreXMLthatisnotwell-formed). TypedandUn-typedxmlColumns WhenyouprovideaschemafortheXMLdocumentsyouwillstore,youcancreateatypedxmlcolumninatable.Youspecifythenameoftheschemacollectionthatcontainstheschemayouwanttoapplytothatcolumn,forexample: CREATETABLEMyTable(MyKeyint,MyXmlxml(MyNewSchemaCol)) NowthecontentoftheXMLdocumentyouinsertintothatcolumnwillbeshreddedautomaticallyintoitsindividualdataitems,andSQLServerwillstoretheseinternallyinthemostefficientandcompactwaypossible.Whenyouquerythecolumn,SQLServerautomaticallyreconstructstheXMLdocumentintoitsoriginalform.Note,however,thatthiswillnotincludethingslikecommentsthatarenotpartoftheoriginaldatacontentofthedocument.Whatyougetbackiseffectivelyaserializedrowsetthatrepresentsthedatayouoriginallystoredthere. It'salsopossibletostoreyourXMLdocumentswithoutspecifyingaschema,inwhichcaseyoucreateanun-typedxmlcolumn.Inthiscase,theXMLisstoredasasimplecharacterstring,becauseSQLServerhasnowayofknowingthedatatypeofeachelementandattribute.Thisislessefficient,butdoesmaintainthecompleteoriginalcontentoftheXMLdocument(suchascomments,etc.).Butrememberthat,evenwithanun-typedcolumn,theXMLyouinsertmustbewell-formed. Tocreateanun-typedcolumn,yousimplyomittheschemacollectionnamewhenyoucreatethetable: CREATETABLEMyTable(MyKeyint,MyXmlxml) Figure2showsSQLServerManagementStudiodisplayingthestructureoftheSales.StoretableintheAdventureWorkssampledatabase.Youcanseethexml-typedcolumnnamedDemographicsintheleft-handtreeview,andaquerythatextractstherowsfromthistableintheright-handquerywindow.Theresultsofrunningthisqueryareshowninthegridbelowthis,andwe'vesuper-imposedonthistheviewoftheXMLdocumentyougetwhenyouclickonthecontentsofoneofthecolumnsinthegrid. Figure2-ThexmlcolumnintheSales.Storetable,showingoneoftheXMLdocumentsitcontains InsertingandSelectingonanxmlColumn Onceyouhavecreatedyourtable,youinsertanXMLdocumentintoanxmlcolumninthesamewayasyouwouldforanyotherbuilt-indatatype.Youcaninsertitasastringvalue,orusetheCASTorCONVERTfunctionstospecificallyconvertittoanxmltype: INSERTINTOMyTable(MyKey,MyXml) VALUES(1,'xml-document-string') INSERTINTOMyTable(MyKey,MyXml) VALUES(1,CAST('xml-document-string')ASxml)) INSERTINTOMyTable(MyKey,MyXml) VALUES(1,CON
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL Server XQuery and XML