SQL Server XQuery and XMLWord文件下载.docx

SQL Server XQuery and XMLWord文件下载.docx

《SQL Server XQuery and XMLWord文件下载.docx》由会员分享，可在线阅读，更多相关《SQL Server XQuery and XMLWord文件下载.docx（60页珍藏版）》请在冰豆网上搜索。

∙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'

<

xsd:

schemaxmlns="

..."

>

...schemacontent...

/xsd:

schema>

'

Youcanaddmultipleschemasinonegobyconcatenatingthemtogether,usetheALTERXMLSCHEMACOLLECTIONstatementtoaddorremoveindividualschemasinacollection,andremovethecollectionusingtheDROPXMLSCHEMACOLLECTIONstatement.SeetheSQLServerhelpfilesformoredetails.

Thenameyouassigntothecollection（"

MyNewSchemaCol"

inthecodeabove）isusedintheALTERandDROPstatements,andisdisplayedinSQLServerManagementStudio.However,youshouldincludeatargetNamespaceattributeintheopening<

elementtoidentifyeachschemainthecollection,forexample:

xs:

schemaxmlns:

xs="

http:

//www.w3.org/2001/XMLSchema"

targetNamespace="

//myns/mydemoschema"

...

/schema>

ThenyoulinkyourXMLdocumentstotheappropriateschemabyspecifyingthisnamespace:

?

xmlversion="

1.0"

encoding="

utf-8"

rootxmlns="

/root>

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'

）

VALUES（1,CAST（'

）ASxml））

VALUES（1,CON