文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
首页
学习
活动
专区
圈层
工具
MCP广场
返回腾讯云官网
社区首页 >问答首页 >从xml中提取值，它具有名称空间和解析xml cdata。

问从xml中提取值，它具有名称空间和解析xml cdata。
EN

Stack Overflow用户
提问于 2021-05-18 08:53:48
回答 1查看 202关注 0票数 1

我试图使用下面的oracle查询从xml中提取值，但它正在重新处理空数据。我不知道我的查询有什么问题，但它适用于常规xml(没有名称空间和CDATA)。如果xml中有CDATA和Namespace，那么有谁知道如何提取值呢？请帮帮忙。提前谢谢。

代码语言：javascript
运行
复制
SELECT EXTRACT (VALUE (a1), '/AttachedDocument/ParentDocumentID/text()').getStringVal () AS ParentDocumentID
      ,EXTRACT (VALUE (a1), '/AttachedDocument/SenderParty/PartyTaxScheme/RegistrationName/text()').getStringVal () AS RegistrationName
                  ,EXTRACT (VALUE (a1), '/AttachedDocument/Attachment/ExternalReference/MimeCode/text()').getStringVal () AS MimeCode
                  ,EXTRACT (VALUE (a1), '/AttachedDocument/Attachment/ExternalReference/Description/DocumentCurrencyCode/text()').getStringVal () AS DocumentCurrencyCode
                  ,EXTRACT (VALUE (a1), '/AttachedDocument/Attachment/ExternalReference/Description/AccountingSupplierParty/Party/PartyName/Name/text()').getStringVal () AS PartyName
FROM 
       TABLE (
          XMLSEQUENCE (
             EXTRACT ( xmltype(
                '<?xml version="1.0" encoding="UTF-8"?>
<AttachedDocument xmlns="urn:oasis:names:specification:ubl:schema:xsd:AttachedDocument-2" xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2" xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2" xmlns:ccts="urn:un:unece:uncefact:data:specification:CoreComponentTypeSchemaModule:2" xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:ext="urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2" xmlns:xades="http://uri.etsi.org/01903/v1.3.2#" xmlns:xades141="http://uri.etsi.org/01903/v1.4.1#">
   <cbc:DocumentType>Test Doc</cbc:DocumentType>
   <cbc:ParentDocumentID>1245</cbc:ParentDocumentID>
   <cac:SenderParty>
      <cac:PartyTaxScheme>
         <cbc:RegistrationName>SSS</cbc:RegistrationName>
         <cbc:CompanyID schemeName="5" schemeID="8" schemeAgencyID="195">11000912</cbc:CompanyID>
         <cac:TaxScheme>
            <cbc:Name>IVA</cbc:Name>
         </cac:TaxScheme>
      </cac:PartyTaxScheme>
   </cac:SenderParty>
   <cac:Attachment>
      <cac:ExternalReference>
         <cbc:MimeCode>text/xml</cbc:MimeCode>
         <cbc:EncodingCode>UTF-8</cbc:EncodingCode>
         <cbc:Description><![CDATA[<?xml version="1.0" encoding="utf-8"?><Invoice xmlns="urn:oasis:names:specification:ubl:schema:xsd:Invoice-2" xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2" xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2" xmlns:ext="urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2" xmlns:sts="dian:gov:co:facturaelectronica:Structures-2-1" xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:xades="http://uri.etsi.org/01903/v1.3.2#" xmlns:xades141="http://uri.etsi.org/01903/v1.4.1#" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <cbc:DocumentCurrencyCode>COP</cbc:DocumentCurrencyCode>
  <cac:AccountingSupplierParty>
    <cbc:AdditionalAccountID schemeAgencyID="195">1</cbc:AdditionalAccountID>
    <cac:Party>
      <cac:PartyName>
        <cbc:Name>First &amp; Sample SSS</cbc:Name>
      </cac:PartyName>
        </cac:AccountingSupplierParty>]]></cbc:Description>
      </cac:ExternalReference>
   </cac:Attachment>
</AttachedDocument>'),
                '/AttachedDocument' ,
                'xmlns="urn:oasis:names:specification:ubl:schema:xsd:AttachedDocument-2" 
                                                                 xmlns:ds="http://www.w3.org/2000/09/xmldsig#" 
                                                                 xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2" 
                                                                 xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2" 
                                                                 xmlns:ccts="urn:un:unece:uncefact:data:specification:CoreComponentTypeSchemaModule:2" 
                                                                 xmlns:ext="urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2" 
                                                                 xmlns:xades="http://uri.etsi.org/01903/v1.3.2#" 
                                                                 xmlns:xades141="http://uri.etsi.org/01903/v1.4.1#"'
                ))) a1
sql
xml
oracle
EN

Stack Overflow用户

回答已采纳

发布于 2021-05-18 10:12:22

如果采用这种方法，则必须在所有extract()子句中声明名称空间，例如：

代码语言：javascript
运行
复制
SELECT EXTRACT (VALUE (a1), '/AttachedDocument/cbc:ParentDocumentID/text()',
                'xmlns="urn:oasis:names:specification:ubl:schema:xsd:AttachedDocument-2" 
                                                                 xmlns:ds="http://www.w3.org/2000/09/xmldsig#" 
                                                                 xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2" 
                                                                 xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2" 
                                                                 xmlns:ccts="urn:un:unece:uncefact:data:specification:CoreComponentTypeSchemaModule:2" 
                                                                 xmlns:ext="urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2" 
                                                                 xmlns:xades="http://uri.etsi.org/01903/v1.3.2#" 
                                                                 xmlns:xades141="http://uri.etsi.org/01903/v1.4.1#"'
       ).getStringVal () AS ParentDocumentID
...

这显然将是混乱和痛苦的；尽管您只需要声明您在XPath中引用的内容。

但是extract()一直都是已弃用，所以除非您使用的是一个非常旧的版本，否则使用XMLTable()会简单得多：

代码语言：javascript
运行
复制
SELECT x1.ParentDocumentID, x1.RegistrationName, x1.MimeCode,
  x2.DocumentCurrencyCode, x2.PartyName
FROM XMLTable (
  XMLNamespaces (
    default 'urn:oasis:names:specification:ubl:schema:xsd:AttachedDocument-2',
    'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' as "cac",
    'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' as "cbc"
  ),
  '/AttachedDocument'
  passing xmltype('<!-- your XML here -->')
  columns ParentDocumentID number path 'cbc:ParentDocumentID',
    RegistrationName varchar2(16) path 'cac:SenderParty/cac:PartyTaxScheme/cbc:RegistrationName',
    MimeCode varchar2(10) path 'cac:Attachment/cac:ExternalReference/cbc:MimeCode',
    Description clob path 'cac:Attachment/cac:ExternalReference/cbc:Description/text()'
) x1
OUTER APPLY XMLTable (
  XMLNamespaces (
    default 'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2',
    'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' as "cac",
    'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' as "cbc"
  ),
  '/Invoice'
  passing XMLType(x1.Description)
  columns DocumentCurrencyCode varchar2(3) path 'cbc:DocumentCurrencyCode',
    PartyName varchar2(50) path 'cac:AccountingSupplierParty/cac:Party/cac:PartyName/cbc:Name'
) x2;

必须将CDATA提取为文本节点，然后将其计算为单独的XMLTable；还请注意，CDATA块中的默认命名空间不同。我忽略了未使用的命名空间。

您的CDATA也是错误的-它丢失了党和发票的结束标签。将这些内容添加到XML文档中：

代码语言：javascript
运行
复制
SELECT x1.ParentDocumentID, x1.RegistrationName, x1.MimeCode,
  x2.DocumentCurrencyCode, x2.PartyName
FROM XMLTable (
  XMLNamespaces (
    default 'urn:oasis:names:specification:ubl:schema:xsd:AttachedDocument-2',
    'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' as "cac",
    'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' as "cbc"
  ),
  '/AttachedDocument'
  passing xmltype('<?xml version="1.0" encoding="UTF-8"?>
<AttachedDocument xmlns="urn:oasis:names:specification:ubl:schema:xsd:AttachedDocument-2" xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2" xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2" xmlns:ccts="urn:un:unece:uncefact:data:specification:CoreComponentTypeSchemaModule:2" xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:ext="urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2" xmlns:xades="http://uri.etsi.org/01903/v1.3.2#" xmlns:xades141="http://uri.etsi.org/01903/v1.4.1#">
   <cbc:DocumentType>Test Doc</cbc:DocumentType>
   <cbc:ParentDocumentID>1245</cbc:ParentDocumentID>
   <cac:SenderParty>
      <cac:PartyTaxScheme>
         <cbc:RegistrationName>SSS</cbc:RegistrationName>
         <cbc:CompanyID schemeName="5" schemeID="8" schemeAgencyID="195">11000912</cbc:CompanyID>
         <cac:TaxScheme>
            <cbc:Name>IVA</cbc:Name>
         </cac:TaxScheme>
      </cac:PartyTaxScheme>
   </cac:SenderParty>
   <cac:Attachment>
      <cac:ExternalReference>
         <cbc:MimeCode>text/xml</cbc:MimeCode>
         <cbc:EncodingCode>UTF-8</cbc:EncodingCode>
         <cbc:Description><![CDATA[<?xml version="1.0" encoding="utf-8"?><Invoice xmlns="urn:oasis:names:specification:ubl:schema:xsd:Invoice-2" xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2" xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2" xmlns:ext="urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2" xmlns:sts="dian:gov:co:facturaelectronica:Structures-2-1" xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:xades="http://uri.etsi.org/01903/v1.3.2#" xmlns:xades141="http://uri.etsi.org/01903/v1.4.1#" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <cbc:DocumentCurrencyCode>COP</cbc:DocumentCurrencyCode>
  <cac:AccountingSupplierParty>
    <cbc:AdditionalAccountID schemeAgencyID="195">1</cbc:AdditionalAccountID>
    <cac:Party>
      <cac:PartyName>
        <cbc:Name>First &amp; Sample SSS</cbc:Name>
      </cac:PartyName>
    </cac:Party>
  </cac:AccountingSupplierParty>
</Invoice>]]></cbc:Description>
      </cac:ExternalReference>
   </cac:Attachment>
</AttachedDocument>')
  columns ParentDocumentID number path 'cbc:ParentDocumentID',
    RegistrationName varchar2(16) path 'cac:SenderParty/cac:PartyTaxScheme/cbc:RegistrationName',
    MimeCode varchar2(10) path 'cac:Attachment/cac:ExternalReference/cbc:MimeCode',
    Description clob path 'cac:Attachment/cac:ExternalReference/cbc:Description/text()'
) x1
OUTER APPLY XMLTable (
  XMLNamespaces (
    default 'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2',
    'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' as "cac",
    'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' as "cbc"
  ),
  '/Invoice'
  passing XMLType(x1.Description)
  columns DocumentCurrencyCode varchar2(3) path 'cbc:DocumentCurrencyCode',
    PartyName varchar2(50) path 'cac:AccountingSupplierParty/cac:Party/cac:PartyName/cbc:Name'
) x2;

产生：

代码语言：javascript
运行
复制
PARENTDOCUMENTID REGISTRATIONNAME MIMECODE   DOCUMENTCURRENCYCODE PARTYNAME
---------------- ---------------- ---------- -------------------- ------------------
            1245 SSS              text/xml   COP                  First & Sample SSS

db<>fiddle

如果XML字符串来自表中的列，则可以将联接/应用交叉到第一个XMLTable子句：

代码语言：javascript
运行
复制
SELECT x1.ParentDocumentID, x1.RegistrationName, x1.MimeCode,
  x2.DocumentCurrencyCode, x2.PartyName
FROM your_table t
CROSS APPLY XMLTable (
  XMLNamespaces (
    default 'urn:oasis:names:specification:ubl:schema:xsd:AttachedDocument-2',
    'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' as "cac",
    'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' as "cbc"
  ),
  '/AttachedDocument'
  passing xmltype(t.xml_string)
  columns ParentDocumentID number path 'cbc:ParentDocumentID',
    RegistrationName varchar2(16) path 'cac:SenderParty/cac:PartyTaxScheme/cbc:RegistrationName',
    MimeCode varchar2(10) path 'cac:Attachment/cac:ExternalReference/cbc:MimeCode',
    Description clob path 'cac:Attachment/cac:ExternalReference/cbc:Description/text()'
) x1
OUTER APPLY XMLTable (
  XMLNamespaces (
    default 'urn:oasis:names:specification:ubl:schema:xsd:Invoice-2',
    'urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2' as "cac",
    'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2' as "cbc"
  ),
  '/Invoice'
  passing XMLType(x1.Description)
  columns DocumentCurrencyCode varchar2(3) path 'cbc:DocumentCurrencyCode',
    PartyName varchar2(50) path 'cac:AccountingSupplierParty/cac:Party/cac:PartyName/cbc:Name'
) x2;

db<>fiddle

如果您所使用的版本不支持apply，那么您可以使用cross join代替；第二个连接更有问题，但是如果您知道您将始终拥有CDATA发票，那么这也可以是交叉连接；在这里11 in 2。

票数 4
EN
查看全部 1 条回答
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/67582973

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2025 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档