elementParentsqlserver2000挂起

SQLServer2000andXMLErikVeermanConsultantIntellinetBusinessIntelligenceObjectivesGiveanoverviewonthenewXMLcapabilitiesinSQLServer2000DemonstrateratherthanpresentProvidebaseknowledgefor…XMLarchitecturedecisionsResearchstartingpointsProsandconsofparallelXMLtechnologyimplementationsDatainteractionleveragingSQL'sXMLcapabilitiesAgendaTheabilitytoretrieveandwriteXMLdata:RetrieveXMLdatausingtheSELECTstatementandtheFORXMLclause.
WriteXMLdatausingOPENXMLrowsetprovider.
TheabilitytoaccessSQLServerusingHTTP.
IISConfigurationTemplateQueriesXSLStylesheetsAgendaSupportforXDRschemas/XMLViewsXPathqueriesagainsttheseschemas.
XMLforSQLWebReleaseBulkLoadUpdategramADOprogrammingmethods(ChrisHagen)XMLinuse(ChrisHagen)Agenda:SQLServer2000XMLSELECTHowhasSQLServerextendedtheSELECTparadigmtosupportXMLSELECTStatementSyntax:SELECT………FORXML{RAW|AUTO|EXPLICIT}[,XMLDATA][,ELEMENTS][,BINARYBase64]SimpleXMLQuery:SELECToh.
CustomerID,oh.
OrderId,od.
ProductID,od.
UnitPrice,od.
QuantityFROMOrdersohINNERJOIN[OrderDetails]odONoh.
orderid=od.
orderidFORXML{RAW|AUTO}FORXMLRAWFormatTakesthequeryresultandtransformseachrowintheresultsetintoanXMLelementwithagenericidentifierastheelementtag.
FORXMLAUTOFormatReturnsqueryresultsinasimple,nestedXMLtree.
EachtableintheFROMclauseforwhichatleastonecolumnislistedintheSELECTclauseisrepresentedasanXMLelement.
ThecolumnslistedintheSELECTclausearemappedtotheappropriateelementattributes.
FORXMLEXPLICITFormatBasedontheconceptofaUniversalTablecontainingalltheinformationabouttheresultingXMLtree.
ContainsTagandParentmeta-dataTag:TagnumberofcurrentelementParent:TagnumberofparentelementColumnnamesareXMLGenericidentifiers.
Identifier!
TagNumber!
AttributeNameEXPLICITSampleQuerySELECT1ASTag,NULLASParent,oh.
CustomerID[Order!
1!
CustomerID],oh.
OrderId[Order!
1!
OrderId],NULL[OrderDetail!
2!
ProductId],NULL[OrderDetail!
2!
UnitPrice],NULL[OrderDetail!
2!
Quantity]FROMOrdersohWHEREoh.
CustomerId='ANTON'UNIONALLSELECT2,1,oh.
CustomerID,oh.
OrderId,od.
ProductID,od.
UnitPrice,od.
QuantityFROMOrdersohINNERJOIN[OrderDetails]odONoh.
orderid=od.
orderidWHERECustomerId='ANTON'ORDERBY[Order!
1!
CustomerID],[Order!
1!
OrderId],[OrderDetail!
2!
ProductId]FORXMLEXPLICITSELECTXMLFormatsDEMOAgenda:SQLServer2000OpenXMLAllowsonetoparseandutilizeanXMLdocumentOpenXMLProcessInputanXMLdocumentProcessaninternalrepresentationexecsp_xml_preparedocumentParsetheXMLdocumentOpenXMLRowsetfunctionRemoveinternalrepresentationfrommemoryEXECsp_xml_removedocumentOpenXMLProcessOpenXMLSyntaxOPENXML(idocint[in],rowpatternnvarchar[in],[flagsbyte[in]])[WITH(SchemaDeclaration|TableName)]OpenXMLProcessDEMOAgenda:SQLServer2000IISIntegrationHowdoesoneuseIISforretrievingXMLdata.
UsingIIS,theprocess.
ConfigureanIISVirtualRootDefinetemplatequeriesDefinestylesheetsMakeHTTPrequestConfigureanIISVirtualRootDEMOHTTPAccesstoDataURLQueryhttp://sqlserver/vrootparametersParametersSql=SELECT+*+FROM+Sku+FOR+XML+RAWEncoding=UTF-8Root=rootHTTPAccesstoDataDirectQueryGoodforasingleselectofasinglecolumnValueisreturnedinnativeformat,notXMLEnablesdirectretrievalofobjectslikeimages,OLEobjects,etc.
http://sqlserver/vroot/dbobject/xpathWhatareTemplateQueriesTheseareXMLdocumentsthatdefinequeriesCanbebounddirectlytoStyleSheetsCalledbyreferencingtheXMLdocumentintheURLWhatareTemplateQueriesTemplatehttp://sqlserver/vroot/vnameparamsVnameisacompletepathtothetemplateXMLfileParametersmaybexsl,encoding,orcontenttype;oruserdefined.
ProvidesaneasywaytoperformcomplexquerieswithlittlenetworktrafficWhyuseTemplateQueriesCanusedynamicSQLbutnorestrictionsonwhatisexecutedAutomaticformattingoftheXMLheaderinformationTemplatessimplifytheHTTPrequestTemplateshavebettersupportparametersDefineandUseStyleSheetsCanbeaddedtotheURLXSL=stylesheet.
xslContentType=text/htmlCanbedefinedintheTemplateWhatdoesatemplatequerylooklikeandhowisitrunDEMOAgenda:XMLXDRSchemaXMLViews|UpdategramMappingrelationaldatathroughanXMLschemaWhatisanXMLViewDefinesanXML-formattedviewonthedatabaseAnnotationsspecifytheXMLtorelationaldatabasemappingforcolumnvaluesandrelationshipsUsesXpathtoquerytheXMLViewHowtouseXMLViewshttp://server/vroot/vname/xpathparametersVnameisadirectreferencetotheschemafileXpathisthexpathqueryParametersmaybexsl,encoding,contenttype,oruserdefinedHowdoIselectdatafromanXMLViewDEMOXMLforSQLWebReleaseXMLforSQLWebRelease1UpdateGramsBulkLoadXMLsupportXMLforSQLWebRelease2(July2001)XMLViewswithXSDSupportforDiffGramsClientSideXMLformattingUpdategramSchemamapping,UpdategramsyntaxInserts,Updates,DeletesUsingandblocksPassingParametersWritingDatawithUpdateGramsUpdategrams(example)MiscellaneousDataRetreival:OLAPdatasp_makewebsqlDEMOAgenda:ADOProgrammingMethodsforSQLServer2000XMLHowdoesonewritecomponentsutilizingthenewSELECTFORXMLandIISUtilizeADOFunctionalityCallsmadethroughADOCommandUsesADOStreamObjectRequiresADO2.
6StreamObjectdefinedasapropertyoftheCommandObjectXMLDataplacedintheStreamObjectObtainedusingReadTextADOsupportsXMLRecordsetscanbepersistedtoXMLFileStreamObjectDOMIntegrationIIS5ResponseObjectAlsosupportforADTGAdvancedDataTableGramformatSupportHierarchicalRecordsetsADOXMLStreamSupportCanpersistdirectlytoanADOStreamObjectXMLextractedusingStreamReadTextXMLdatacanthenbeutilizedinmemoryPassXMLbacktoclientTransformtoHTMLusingXSLADOandDOMIntegrationCanpersistXMLdirectlyintotheXMLDOMDocumentRecordset.
SaveMSXML.
DOMDocument,adPersistXMLCanperformdirectXSLtransformforHTMLbasedcomponentsADOandIIS5ResponseResponseObjectexposesandIStreamInterfaceRecordset.
SaveResponse,adPersistXMLDisplaysXMLinbrowserAvoidsexpensivediskoperationsADOXMLProgrammingDEMOForfurtherInformationXMLwebsitehttp://msdn.
microsoft.
com/xmlSQLServer2000andIIShttp://msdn.
microsoft.
com/msdnmag/issues/0300/sql/sql.
aspSQLServer2000http://www.
microsoft.
com/sqlADOhttp://www.
microsoft.
com/adoFeedback/Questions