在Sql Server 2005中将主子表关系的XML文档转换成主子表“Join”形式的表

时间:2023-02-13 11:09:32

最近这段时间在Sql Server 2005下做了很多根据复杂XML文档导入数据表,以及根据数据表生成复杂XML文档的事情(并非 For XML Auto了事),所有的操作都是利用Sql语句,发现Sql Server 2005的XML文档处理能力真的已经很强了,自己也终于开始体会到Sql Server 2005真正的实力了。在这里记录一下这种情况的处理:

有以下一个XML文档:

 

< basevendors >
    
< basevendor  name ="Northeast"  taxid ="99999"  description ="Re/Max Northeast"  activevendor ="Y"  apvendornumber ="8888" >
        
< basevendorcontactinfo >
            
< basevendoraddress  addressline1 ="2940 Oak St."  City ="Kingwood"  statecode ="TX"  zip ="77339"  country ="USA"  effectivedate ="11/11/2001"   />
            
< basevendoraddress  addressline1 ="1849 Kingwood Dr."  City ="Kingwood"  statecode ="TX"  zip ="0"  country ="USA"   />
        
</ basevendorcontactinfo >
    
</ basevendor >
    
< basevendor  name ="Better Homes &amp; Gardens Rand Realty"  taxid ="321456"  description ="Better Homes &amp; Gardens Rand Realty"  activevendor ="Y"  apvendornumber ="87542" >
        
< basevendorcontactinfo >
            
< basevendoraddress  addressline1 ="10 Schriever Lane"  City ="New City"  statecode ="NY"  zip ="10956"  country ="USA"  effectivedate ="11/22/1899"   />
        
</ basevendorcontactinfo >
    
</ basevendor >
    
< basevendor  name ="Bodell-Van Drimmelen"  taxid ="9856587"  description ="Bodell-Van Drimmelen"  activevendor ="N"  apvendornumber ="22545" >
        
< basevendorcontactinfo >
            
< basevendoraddress  addressline1 ="Residential Appraisers, Inc."  City ="Salt Lake City"  statecode ="UT"  zip ="84106"  country ="USA"  effectivedate ="04/29/2003" />
            
< basevendoraddress  addressline1 ="Residential Appraisers, Inc."  City ="Salt Lake City"  statecode ="UT"  zip ="0"  country ="USA"  effectivedate ="04/11/2003"   />
        
</ basevendorcontactinfo >
    
</ basevendor >
    
< basevendor  name ="Rental Relocation Inc."  taxid ="6589654"  description ="Rental Relocation Inc."  activevendor ="Y"  apvendornumber ="778855"   />
</ basevendors >

 

 其中包含主子表关系,主表是basevendor节点的信息,包括name, taxid等内容,子表信息包含在每个basevendor节点下的basevendoraddress节点的属性中,包括addressline1, city等信息。

现在假设有这样一个数据表:

CREATE   TABLE  BaseVendorAndAddress
(
    BaseVendorName 
VARCHAR ( 50 )
    , BaseVendorTaxId 
VARCHAR ( 20 )
    , AddressLine 
VARCHAR ( 100 )
    , City 
VARCHAR ( 20 )
)

 

其中前2个字段来自于主表,而后面2个字段来自于子表

如何操作呢?Sql Server 2005太强大了(各位高手请勿蔑视小生这种“没见过世面”的夸张),以下是处理方法:

DECLARE   @XML  XML
SET   @XML =   '
<basevendors>
    ... 上面那段XML文档 ...
</basevendors>
'

SELECT  Vendor.value( ' @name[1] ' , ' varchar(50) ' AS  VendorName, Vendor.value( ' @taxid[1] ' , ' varchar(50) ' AS  TaxID
    , addr.value(
' @addressline1[1] ' , ' varchar(200) ' AS  AddressLine, addr.value( ' @City[1] ' , ' varchar(10) ' AS  City
FROM   @XML .nodes( ' basevendors/basevendor ' ) BV(Vendor)
CROSS   APPLY  BV.Vendor.nodes( ' basevendorcontactinfo/basevendoraddress ' ) addrs(addr)

 

利用Sql Server 2005处理XML类型的能力和Apply操作,寥寥几句就解决了。