xmlexists with namespace in Oracle

Question

I am having issues figuring out how to integrate namespaces to a where clause having xmlexists. The query is as below:

SELECT sing_d1.sing_info_text1, sing_d1.sing_info_text2
FROM XMLTABLE(
      xmlnamespaces (
                       'http://www.abccompany.com/Canonical' AS "abc",
                       'http://abccompany.com/PmtInfo' AS "pmt",
                       'http://abccompany.com/CommonTypes' AS "cmn",
                       'http://www.w3.org/2001/XMLSchema-instance' AS "xsi"),
                   '/abc:abc/abc:Pmt/pmt:Payments/pmt:PayInfo/pmt:Single/pmt:SingAddInfo'
                    PASSING payXml
                    COLUMNS 
                                  sing_info_type VARCHAR2(4000)
                                  PATH 'cmn:AddInfoCmpType',
                                  sing_info_text1 VARCHAR2(4000)
                                  PATH 'cmn:AddInfoCmpText[1]',
                                  sing_info_text2 VARCHAR2(4000)
                                  PATH 'cmn:AddInfoCmpText[2]'
              )sing_d1
WHERE 
                       XMLExists(

                   '/abc:abc/abc:Pmt/pmt:Payments/pmt:PayInfo/pmt:Single/pmt:SingAddInfo[AddInfoCmpType = "IATCode"]'

);

Any clues would be highly appreciated.

Would it not be simpler to write WHERE sing_info_type = 'IATCode'? — Wernfried Domscheit
– Wernfried Domscheit, Commented Nov 7, 2016 at 14:44

Arkadiusz Łukasiewicz · Accepted Answer · 2016-11-07 15:20:36Z

2

   XMLExists('   declare namespace xsi="http://www.w3.org/2001/XMLSchema-instance";
                declare namespace abc="http://www.abccompany.com/Canonical";
                        declare namespace pmt="http://abccompany.com/PmtInfo";
                        declare namespace cmn="http://abccompany.com/CommonTypes";
                          /abc:abc/abc:Pmt/pmt:Payments/pmt:PayInfo/pmt:Single/pmt:SingAddInfo[AddInfoCmpType = "IATCode"]' passing payXml)

For xmlns(default) use declare default element namespace "http.url.url". For other declare namespace xyz

edited Nov 7, 2016 at 15:20

answered Nov 7, 2016 at 14:40

Arkadiusz Łukasiewicz

6,3761 gold badge13 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

QuickDrawMcgraw Over a year ago

Thanks AL. But now the code which is in a procedure is failing with FAIL:Others errorORA-19228: XPST0008 - undeclared identifier: prefix '.' local-name ''.

QuickDrawMcgraw Over a year ago

I do not know what i was thinking. I need to actually use the xmlexists functionality before the select, cause a where clause with the xmlexists will still make the select fail with no_data_found if the tag is missing from an xml. Can xmlexists be used with an IF clause to check if the data exists before going into the select?

Arkadiusz Łukasiewicz Over a year ago

select case when XMLExists('/a/b' passing xmltype('<a><b></b></a>')) then 'YES' else 'NO' end into var from dual; / select EXISTSNODE(xmltype('<a><b></b></a>'), '/a/b') into var from dual\ / In EXISTSNODE namespaces go as last paramter ,'xmlns:abc="url.abc" xmlns:xyz="url.xyz"'

Collectives™ on Stack Overflow

xmlexists with namespace in Oracle

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related