正文转载:http://www.cnblogs.com/Ricky81317/archive/2010/01/06/1640434.html

 

近些年及时段时以Sql Server
2005产开了不少因复杂XML文档导入数据表,以及因数据表生成复杂XML文档的业务(并非
For XML Auto了事),所有的操作都是动Sql语句,发现Sql Server
2005底XML文档处理能力确实已经非凡强了,自己呢好不容易起初体会到Sql Server
2005真的实力了。在此记录转这种气象的拍卖:

有以下一个XML文档:

图片 1)

<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>

图片 2)

里头含主子表关系,主表是basevendor节点的音讯,包括name,
taxid等情节,子表消息包含在每个basevendor节点下的basevendoraddress节点的特性被,包括addressline1,
city等信息。

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

图片 3)

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

图片 4)

中间后边2个字段来自于主表,而前边2单字段来自于子表

哪些操作为?Sql Server
2005太强了(各位高手请不蔑视小生这种“没见了世面”的浮夸),以下是拍卖措施:

图片 5)

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)

图片 6)

选择Sql Server 2005拍卖XML类型的能力跟Apply操作,寥寥几句就缓解了。

相关文章

网站地图xml地图