SQL Server XQuery and XMLWord格式文档下载.docx
- 文档编号:8435419
- 上传时间:2023-05-11
- 格式:DOCX
- 页数:60
- 大小:498.37KB
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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL Server XQuery and XML