时间戳

首页 > 解决方案 > Xquery TSQL：如何在遍历具有多个 xmlns、xmlns:xsd 和 xmlns:xsi 的父节点时检索元素数据

xml - Xquery TSQL：如何在遍历具有多个 xmlns、xmlns:xsd 和 xmlns:xsi 的父节点时检索元素数据

问题描述

我需要在 processquotedetail 元素中检索“真实”值。

我必须提供什么 xmlnamespaces，并在外部应用中指定才能成功检索“true”

我改变了 xmlns: 值

我已经尝试了以下和其他几个轻微的替代方案，所有这些都返回 null。您能解释一下为什么下面的方法不起作用，以及为什么起作用吗？

谢谢

查询： 

WITH XMLNAMESPACES('web.servivces.com"' as  pmq,
                    'www.test.com/' as q,
                    'www.test.com/request/' as r )

select 
x.RequestID,       dc.tx.value('pmq:Control[1]/pmq:ProcessQuoteDetail[1]','nvarchar(max)')
dc.tx.value('Control[1]/ProcessQuoteDetail[1]','nvarchar(max)')

from [dbo].[uvw_decompressXML_DC_Quote] x  

outer apply x.DCRequest.nodes('server/requests/Session.setDocumentRq/session/data/policy
r:AdditionalData/r:KeyValuePair/q:Key/q:Value/pmq:PMQuoteData') as dc(tx)

XML：

XML: 
<server>
  <requests>
    <Session.loginRq userName="admin" password="admin" />
    <Session.setDocumentRq>
      <session>
        <data>
          <MotorQuoteID />
          <TransactionReason>Rating</TransactionReason>
          <policy>
            <AdditionalData xmlns="www.test.com/request/" 
             xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
              <KeyValuePair>
                <Key xmlns="www.test.com/">AggregatorXML</Key>
                <Value xmlns="www.test.com/">
                  <PMQuoteData xmlns="web.servivces.com">
                    <Control>
                      <ProcessQuoteDetail>true</ProcessQuoteDetail>

标签： xmlxpathxqueryxml-namespaces

解决方案

SQL

-- DDL and data population, start
DECLARE @tbl TABLE (ID INT IDENTITY(1,1) PRIMARY KEY, xmlData XML NOT NULL);
INSERT INTO @tbl
VALUES
('<server>
    <requests>
        <Session.loginRq userName="admin" password="admin"/>
        <Session.setDocumentRq>
            <session>
                <data>
                    <MotorQuoteID/>
                    <TransactionReason>Rating</TransactionReason>
                    <policy>
                        <AdditionalData xmlns="www.test.com/request/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                            <KeyValuePair>
                                <Key xmlns="www.test.com/">AggregatorXML</Key>
                                <Value xmlns="www.test.com/">
                                    <PMQuoteData xmlns="web.servivces.com">
                                        <Control>
                                            <ProcessQuoteDetail>true</ProcessQuoteDetail>
                                        </Control>
                                    </PMQuoteData>
                                </Value>
                            </KeyValuePair>
                        </AdditionalData>
                    </policy>
                </data>
            </session>
        </Session.setDocumentRq>
    </requests>
</server>');
-- DDL and data population, end

WITH XMLNAMESPACES('web.servivces.com' as ns3,
                    'www.test.com/' as ns2,
                    'www.test.com/request/' as ns1 )
SELECT tbl.id, col.value('(ns3:ProcessQuoteDetail)[1]', 'VARCHAR(10)') as [ProcessQuoteDetail]
FROM @tbl AS tbl
CROSS APPLY tbl.XMLDATA.nodes('/server/requests/Session.setDocumentRq/session/data/policy/ns1:AdditionalData/ns1:KeyValuePair/ns2:Value/ns3:PMQuoteData/ns3:Control') tab(col);

推荐阅读