求助。。。sql查询带有namespace的xml的几个问题。。。

我想查询publish根节点下的body节点的内容，我的sql如下DECLARE @xml XML
SET @xml='<Publish xmlns="http://soa.com/SOA/USA/InfrastructureService/V30/PubSubService">
    <RouteTable>
    <Article>
      <ArticleCategory>RMANotification</ArticleCategory>
      <ArticleType1>Sync</ArticleType1>
      <ArticleType2>IPPToWCQX</ArticleType2>
    </Article>
  </RouteTable>
  <Node>
    <MessageHead>    
      <Action>Notify</Action>    
      <Type>RMA</Type>
      <CompanyCode>123456789</CompanyCode>    
      <FromSystem>IPP</FromSystem>
      <ToSystem>WCQX</ToSystem>
    </MessageHead>
    <Body>    
      <ChannelRMA>
        <Version>1.0</Version>
        <AppKey>90000001</AppKey>      
        <Method />       
        <Session />
        <TimeStamp />
        <RMAID>85411254</RMAID>      
        <RMARefundAmount>100.00</RMARefundAmount>
        <RMAItems>
          <RMAItem>
            <RMAItemID>123456</RMAItemID>
            <ProcessType>Refund</ProcessType>          
          </RMAItem>
          <RMAItem>
            <RMAItemID>123457</RMAItemID>
            <ProcessType>Return</ProcessType>
          </RMAItem>          
        </RMAItems>                
      </ChannelRMA>
    </Body>
  </Node>
</Publish>'
;WITH  XMLNAMESPACES (DEFAULT  'http://soa.com/SOA/USA/InfrastructureService/V30/PubSubService')
SELECT @xml.query('(/Publish/Node/Body/ChannelRMA)[1]') 
但是查出来的xml就变成了下面这样：
<p1:ChannelRMA xmlns:p1="http://soa.com/SOA/USA/InfrastructureService/V30/PubSubService">
  <p1:Version>1.0</p1:Version>
  <p1:AppKey>90000001</p1:AppKey>
  <p1:Method />
  <p1:Session />
  <p1:TimeStamp />
  <p1:RMAID>85411254</p1:RMAID>
  <p1:RMARefundAmount>100.00</p1:RMARefundAmount>
  <p1:RMAItems>
    <p1:RMAItem>
      <p1:RMAItemID>123456</p1:RMAItemID>
      <p1:ProcessType>Refund</p1:ProcessType>
    </p1:RMAItem>
    <p1:RMAItem>
      <p1:RMAItemID>123457</p1:RMAItemID>
      <p1:ProcessType>Return</p1:ProcessType>
    </p1:RMAItem>
  </p1:RMAItems>
</p1:ChannelRMA>
怎么自动加了个p1的标签还有namespace，怎么才能让查出来的结果是下面这样呢：    <ChannelRMA>
        <Version>1.0</Version>
        <AppKey>90000001</AppKey>      
        <Method />       
        <Session />
        <TimeStamp />
        <RMAID>85411254</RMAID>      
        <RMARefundAmount>100.00</RMARefundAmount>
        <RMAItems>
          <RMAItem>
            <RMAItemID>123456</RMAItemID>
            <ProcessType>Refund</ProcessType>          
          </RMAItem>
          <RMAItem>
            <RMAItemID>123457</RMAItemID>
            <ProcessType>Return</ProcessType>
          </RMAItem>          
        </RMAItems>                
      </ChannelRMA>

解决方案 »

  1.   

    设法把xml数据中的xmlns去掉，然后把后面的WITH也去掉就可以了
    DECLARE @xml XML
    SET @xml='<Publish>
        <RouteTable>
        <Article>
          <ArticleCategory>RMANotification</ArticleCategory>
          <ArticleType1>Sync</ArticleType1>
          <ArticleType2>IPPToWCQX</ArticleType2>
        </Article>
      </RouteTable>
      <Node>
        <MessageHead>    
          <Action>Notify</Action>    
          <Type>RMA</Type>
          <CompanyCode>123456789</CompanyCode>    
          <FromSystem>IPP</FromSystem>
          <ToSystem>WCQX</ToSystem>
        </MessageHead>
        <Body>    
          <ChannelRMA>
            <Version>1.0</Version>
            <AppKey>90000001</AppKey>      
            <Method />       
            <Session />
            <TimeStamp />
            <RMAID>85411254</RMAID>      
            <RMARefundAmount>100.00</RMARefundAmount>
            <RMAItems>
              <RMAItem>
                <RMAItemID>123456</RMAItemID>
                <ProcessType>Refund</ProcessType>          
              </RMAItem>
              <RMAItem>
                <RMAItemID>123457</RMAItemID>
                <ProcessType>Return</ProcessType>
              </RMAItem>          
            </RMAItems>                
          </ChannelRMA>
        </Body>
      </Node>
    </Publish>'
    --;WITH  XMLNAMESPACES (DEFAULT  'http://soa.com/SOA/USA/InfrastructureService/V30/PubSubService')
    SELECT @xml.query('(/Publish/Node/Body/ChannelRMA)[1]') 
      

  2.   

    有没有不用去namespace的方法啊？
      

  3.   


    DECLARE @xml XML
    SET @xml='
    <Publish xmlns="http://soa.com/SOA/USA/InfrastructureService/V30/PubSubService">
        <RouteTable>
        <Article>
          <ArticleCategory>RMANotification</ArticleCategory>
          <ArticleType1>Sync</ArticleType1>
          <ArticleType2>IPPToWCQX</ArticleType2>
        </Article>
      </RouteTable>
      <Node>
        <MessageHead>    
          <Action>Notify</Action>    
          <Type>RMA</Type>
          <CompanyCode>123456789</CompanyCode>    
          <FromSystem>IPP</FromSystem>
          <ToSystem>WCQX</ToSystem>
        </MessageHead>
        <Body>    
          <ChannelRMA>
            <Version>1.0</Version>
            <AppKey>90000001</AppKey>      
            <Method />       
            <Session />
            <TimeStamp />
            <RMAID>85411254</RMAID>      
            <RMARefundAmount>100.00</RMARefundAmount>
            <RMAItems>
              <RMAItem>
                <RMAItemID>123456</RMAItemID>
                <ProcessType>Refund</ProcessType>          
              </RMAItem>
              <RMAItem>
                <RMAItemID>123457</RMAItemID>
                <ProcessType>Return</ProcessType>
              </RMAItem>          
            </RMAItems>                
          </ChannelRMA>
        </Body>
      </Node>
    </Publish>';
    select @xml.query('
    declare default element namespace "http://soa.com/SOA/USA/InfrastructureService/V30/PubSubService";
    /Publish/Node/Body/ChannelRMA')/*
    <ChannelRMA xmlns="http://soa.com/SOA/USA/InfrastructureService/V30/PubSubService">
      <Version>1.0</Version>
      <AppKey>90000001</AppKey>
      <Method />
      <Session />
      <TimeStamp />
      <RMAID>85411254</RMAID>
      <RMARefundAmount>100.00</RMARefundAmount>
      <RMAItems>
        <RMAItem>
          <RMAItemID>123456</RMAItemID>
          <ProcessType>Refund</ProcessType>
        </RMAItem>
        <RMAItem>
          <RMAItemID>123457</RMAItemID>
          <ProcessType>Return</ProcessType>
        </RMAItem>
      </RMAItems>
    </ChannelRMA>
    */
      

  4.   

    这个方法确实能实现，怎么写才能让结果中不带namespace呢？求教啊。
      

  5.   

    用个replace就可以去掉xml数据中的xmlns，除了此法你只能在输出xml数据的时候将xmlns去除了