SQL Server XQuery and XML.docx

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,youshouldincludeatargetNamespaceattributeintheopeningelementtoidentifyeachschemainthecollection,forexample:

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