Techniques for Storing XML

TechniquesforStoringXMLXMLGroup,IDKE,RUC2002/5/202Outline1DataorDocument?Data-CentricDocument-Centric2StoringData-CentricXMLDatainXML-EnanbledDBMS3Commercial(O)DBMSSupportForXML4IntroductiontoXMLBenchMark3I.DataorDocument?4ClassesofXMLDocumentsData-centricDocumentproductcatalogs,inventorydata,medicalrecords,networkmessages,logs,stockquotesmachineconsumptionDocument-centricDocumentUsermanualsHumanconsumptionMixed5Data-CentricExamplecompanyid=id2_3cnameOracle/cnameaddressstreet1435Bayave./streetcityRedwoodShores/citystateCA/statezip11234/zip/address…6Document-CentricExampleaction-descsponsorMr.English/sponsorintroducedthefollowingbill;whichwasreferredtothecommittee-nameCommitteeonFinancialServices/committee-name.../action-desc7MixedExamplebillbill-stage=Introductioncongress110thCONGRESS/congresssession1stSession/sessionlegis-numH.R.133/legis-numaction-descsponsorMr.English/sponsorintroducedthefollowingbill………/bill8II.StoringData-CentricXMLDatainXML-EnanbledDBMS9TwoMethodsCLOBMappingXMLDatatoDBMSTable10CLOBTheSimplestWay!StrongFull-TextSearchCompatibleWithSQLExtensibleByAddingFunctions.Drawbacks:PieceUpdatingNOTsupportedXML-specificQueryNOTsupported11CLOBExamples:Oracle9iXMLTypeDB2XMLCLOB——大的XML文档，类似于Oracle的XMLType；XMLVARCHAR——小的XML文档XMLFile——外部的XML文档12TwoMethodsCLOBMappingXMLDatatoDBMSTable13MappingSchema-LessMappingSchema-basedMapping14Schema-LessMappingDefinition:Edge(source,ordinal,name,flag,target)Value(vid,value)Rules:storealledgesinonetableScalarvaluesstoredinseparatetable15EdgeRelationExample&0HL7&1PATIENT&2PIDOBX&3&4…@IDNumPaNaDTofBi&5&6&7PATID1234“JonesWm”date&81961-06-13sourceordinaltagflagtarget&01HL7ref&1&11PATIENTref&2&21PIDref&3&22OBXref&4&31IDNumstring&5&32PaNastring&6&33DTofBiref&7NodeValue&5PATID1234&6JonesWilliamEdgeTableValueTable16EdgeTable优点：不依赖于XMLDTD（Schema），不许事先建立映射关系可以还原源文档，但开销巨大缺点：大量的连接，导致低效17MappingSchema-LessMappingSchema-basedMapping18Schema-basedMappingRulesRule1:ComplexElement:Complexelementtypesgenerateclasstableswithprimarykeycolumns.19Schema-basedMappingRulesRule2:Simpleelement:Singlereferencestosimpleelementtypesgeneratecolumns;ifthereferenceisoptional(?operator),thecolumnisnullable.Repeatedreferencestosimpleelementtypesgeneratepropertytableswithforeignkeys.Referencestocomplexelementtypesgenerateforeignkeysinremoteclasstables.PCDATAinmixedcontentgeneratesapropertytablewithaforeignkey.OptionallygenerateordercolumnsforallreferencedelementtypesandPCDATA.20Schema-basedMappingRulesRule3:AttirbutesSingle-valuedattributesgeneratecolumns;iftheattributeisoptional,thecolumnisnullable.Multi-valuedattributesgeneratepropertytableswithforeignkeys.Ifanattributehasadefault,itisusedasthecolumndefault.21Schema-basedMappingExample!ELEMENTOrder(OrderNum,Date,CustNum,Item*)!ELEMENTOrderNum(#PCDATA)!ELEMENTDate(#PCDATA)!ELEMENTCustNum(#PCDATA)!ELEMENTItem(Quantity,Part)!ATTLISTItemNumID#REQUIRED!ELEMENTQuantity(#PCDATA)!ELEMENTPart(PartNum,Price)!ELEMENTPartNum(#PCDATA)!ELEMENTPrice(#PCDATA)22TableOrderColumnOrderPKColumnOrderNumColumnDateColumnCustNumTableItemColumnItemPKColumnItemNumColumnQuantityColumnOrderFKTablePartColumnPartPKColumnPartNumColumnPriceColumnPartFK23III.Commercial(O)DBMSSupportForXML24TwoMethodsCLOBOracle9iXMLTypeMappingXMLDatatoDBMSTable25XMLType（介绍）1XMLType是Oracle定义的一种数据对象CREATETABLEwarehouses(warehouse_idNUMBER(3)，warehouse_specSYS.XMLTYPE,warehouse_nameVARCHAR2(35),location_idNUMBER(4));26XMLType（介绍）2插入操作如：INSERTintowarehouses(warehouse_id,warehouse_spec)VALUES(1001,sys.XMLType.createXML('?xmlversion=1.0?WarehousewhNo=“200BuildingOwned/Building/Warehouse'));27XMLTpye（介绍）3XMLType不支持pieciesupdate如果要把BuildingOwned/Building改为BuildingLoaned/Building：UPDATEwarehousesSETwarehouse_spec=sys.XMLType.createXML('?xmlversion=1.0?Warehousewhono=200BuildingLoaned/Building/Warehouse'));28XMLType（方法）1Extract（）和ExistNode（）其它方法包括：creatXML（）、isFragment()、getStringVal()、getNumberVal（）通过这些内建的方法，实现与SQL的交互。如：29XMLType（方法）2SELECTw.warehouse_spec.extract('//Building/text()').getStringVal()asbuldingFROMwarehouseswWHEREw.warehouse_spec.existsNode('/Warehouse/Building')=1ANDwarehouse_id=100130XMLType（OracleText）1Oracle用OracleText（interMedia的一种）来进行对XMLType的索引和查找。它的主要函数是Contains()，为了支持XML的Xpath，引入了两个新的函数hasPath()和inPath()。31XMLType（OracleText）2hasPath（）的使用：找出po_xml_tab的poDoc中有CUSTOMER元素的所有元组。（因为CUSTOMRE是可选的元素）SELECT*FROMpo_xml_tabwWHERECONTAINS(w.poDoc,'hasPath(/PO/CUSTOMER)')0;32XMLType（OracleText）3InPath()的使用：找出路径/PO/CITY中出现了Edison的所有元组。SELECT*FROMpo_xml_tabwWHERECONTAINS(w.poDoc,'EdisoninPath(/PO/CITY)')0;33XMLType（OracleText）4withIn()的使用找出CUSTNAME属性中出现John的所有元组：SELECT*FROMpo_xml_tabwWHERECONTAINS(w.poDoc,'JohnwithIn/PO/CUSTOMER@CUSTNAME')0;34XMLType（索引）1针对Element或Attribute的索引有些Element或者Attribute可能经常查询，这时可以建立相应的索引，如：SELECT*FROMpo_xml_tabeWHEREe.poDoc.extract('//PONO/text()').getNumberVal()=100