最近这段时间在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 & Gardens Rand Realty" taxid ="321456" description ="Better Homes & 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 ="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 & Gardens Rand Realty" taxid ="321456" description ="Better Homes & 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 )
)
(
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)
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操作,寥寥几句就解决了。