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

最近这段时间在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操作，寥寥几句就解决了。