sql 解析，还原xml

<Publish xmlns="http://soa.newegg.com/SOA/CN/InfrastructureService/V10/NeweggCNECPubSubService">
  <Subject>E5QRY02ECCustomerBasic</Subject>
  <FromService>http://soa.newegg.com/SOA/CN/InfrastructureService/V10/NeweggCN/ECPubSubService</FromService>
  <ToService>http://soa.newegg.com/SOA/CN/CustomerMasterProfile/V10/E5QRY02/ECCustomerBasic</ToService>
  <Node>
    <Message>
      <Header>
        <Version>V11</Version>
        <Type />
        <GlobalBusinessType>Listing</GlobalBusinessType>
        <StoreCompanyCode>8601</StoreCompanyCode>
        <TransactionCode>03-001-0-006</TransactionCode>
      </Header>
      <Body>
        <CustomerShippingAddressInfo>
          <Name>上海美达建筑工程有限公司</Name>
          <Contact>姚杰</Contact>
          <Phone>64731590</Phone>
          <CellPhone>13917136121</CellPhone>
          <Fax />
          <Address>上海市陕西南路弄号二楼</Address>
          <Zip>200020</Zip>
          <Customer name=”AAA”>>
            <ID>10000</ID> 
            <CompanyType>0</CompanyType>
            <RegisterTime>2011-01-01T00:00:00</RegisterTime>
            <InitTotalSOMoney>0</InitTotalSOMoney>
          </Customer>
          <Customer name=”BBB”>
            <ID>2</ID>
            <CompanyType>0</CompanyType>
            <RegisterTime>2010-01-01T00:00:00</RegisterTime>
            <InitTotalSOMoney>10</InitTotalSOMoney>
          </Customer>
        </CustomerShippingAddressInfo>
      </Body>
    </Message>
  </Node>
</Publish>求达人帮忙解析还原这个xml

解决方案 »

  1.   

    "<Publish xmlns="
    XML命名空间声明有误,不解释.
      

  2.   

    --<Publish>
    ;WITH xmlnamespaces (default 'http://soa.newegg.com/SOA/CN/InfrastructureService/V10/NeweggCNECPubSubService')
    select T1.ST.value('(./Subject/text())[1]','varchar(100)') as Subject--<Subject>
        ,T1.ST.value('(./FromService/text())[1]','varchar(200)') as FromService--<FromService>
    ,T1.ST.value('(./ToService/text())[1]','varchar(200)') as ToService--<ToService>
    into #Publish
    from @x.nodes('/Publish') as T1(ST)
    select * from #Publish--OK<Publish ><Node><Message><Header>
    ;WITH xmlnamespaces (default 'http://soa.newegg.com/SOA/CN/InfrastructureService/V10/NeweggCNECPubSubService')
    select 
    T1.ST.value('(./Node/Message/Header/Version/text())[1]','nvarchar(100)')  as Version--<Header/.>
    ,T1.ST.value('(./Node/Message/Header/Type/text())[1]','nvarchar(100)')  as Type--<Header/.>
    ,T1.ST.value('(./Node/Message/Header/GlobalBusinessType/text())[1]','nvarchar(100)')  as GlobalBusinessType
    ,T1.ST.value('(./Node/Message/Header/StoreCompanyCode/text())[1]','int') as StoreCompanyCode
    ,T1.ST.value('(./Node/Message/Header/TransactionCode/text())[1]','varchar(50)') as  TransactionCode
    into #Header
    from @x.nodes('/Publish') as T1(ST)
    select * from #Header--OK<Publish ><Node><Message><Body><CustomerShippingAddressInfo> 
    ;WITH xmlnamespaces (default 'http://soa.newegg.com/SOA/CN/InfrastructureService/V10/NeweggCNECPubSubService')
    select
    T1.ST.value('(./Node/Message/Body/CustomerShippingAddressInfo/Name/text())[1]','nvarchar(100)') as  Name
    ,T1.ST.value('(./Node/Message/Body/CustomerShippingAddressInfo/Contact/text())[1]','nvarchar(100)') as  Contact
    ,T1.ST.value('(./Node/Message/Body/CustomerShippingAddressInfo/Phone/text())[1]','int') as  Phone
    ,T1.ST.value('(./Node/Message/Body/CustomerShippingAddressInfo/CellPhone/text())[1]','bigint') as  CellPhone
    ,T1.ST.value('(./Node/Message/Body/CustomerShippingAddressInfo/Fax/text())[1]','int') as  Fax
    ,T1.ST.value('(./Node/Message/Body/CustomerShippingAddressInfo/Address/text())[1]','nvarchar(100)') as  Address
    ,T1.ST.value('(./Node/Message/Body/CustomerShippingAddressInfo/Zip/text())[1]','int') as  Zip
    into #CustomerShippingAddressInfo
    from @x.nodes('/Publish') as T1(ST)
    select * from #CustomerShippingAddressInfo--OK<Publish ><Node><Message><Body><CustomerShippingAddressInfo><Customer > 
    ;WITH xmlnamespaces (default 'http://soa.newegg.com/SOA/CN/InfrastructureService/V10/NeweggCNECPubSubService')
    select
    T2.C.value('(./@name)[1]','nvarchar(50)') as name
    ,T2.C.value('(./ID/text())[1]','int') as ID
    ,T2.C.value('(./CompanyType/text())[1]','int') as CompanyType
    ,T2.C.value('(./RegisterTime/text())[1]','varchar(100)') as RegisterTime
    ,T2.C.value('(./InitTotalSOMoney/text())[1]','money') as InitTotalSOMoney
    into #Customer
    from @x.nodes('/Publish') as T1(ST)
    CROSS APPLY @x.nodes('/Publish/Node/Message/Body/CustomerShippingAddressInfo/Customer') as T2(C)
    select * from #Customer--还原
    ;WITH xmlnamespaces (default 'http://soa.newegg.com/SOA/CN/InfrastructureService/V10/NeweggCNECPubSubService')
    select 
    Subject as 'Subject'
    ,FromService as 'FromService'
    ,ToService as 'ToService'
    ,[Node/Message]=(
    select 
    (select 
    V.Version as 'Version'
    ,V.Type as  'Type'
    ,V.GlobalBusinessType as 'GlobalBusinessType'
    ,V.StoreCompanyCode as 'StoreCompanyCode'
    ,V.TransactionCode as 'TransactionCode'
    from #Header as V
    for xml path('Header'),ELEMENTS XSINIL,type)

    ,[Body/CustomerShippingAddressInfo]=
    (select 
    (select 
    CAI.Name as 'Name'
    ,CAI.Contact as 'Contact'
    ,CAI.Phone as 'Phone'
    ,CAI.CellPhone as 'CellPhone'
    ,CAI.Fax as 'Fax'
    ,CAI.Address as 'Address'
    ,CAI.Zip as 'Zip'
    from #CustomerShippingAddressInfo as CAI
    for xml path(''),ELEMENTS XSINIL,type)
    ,(select 
    CU.name as '@name'
    ,CU.ID as 'ID'
    ,CU.CompanyType as 'CompanyType'
    ,CU.RegisterTime as 'RegisterTime'
    ,CU.InitTotalSOMoney as 'InitTotalSOMoney'   
    from #Customer as CU
    for xml path('Customer'),type
    )

    for xml path(''),type
    )

    for xml path(''),type


    )
    from #Publish 
    for xml path('Publish')
      

  3.   

     在  <Customer name="AAA">>处多了一个'>'去掉一个