Hi Guys, Hope you all are doing good.
I'm facing issue while fetching the data from nested nullable namespace using XMLTABLE.
From the following XML string, the moment I remove ' xmlns="" ' from 'documents' tags, i am start getting the data.
Could you please help/guide me to resolve this issue?
select xt.*
from
(
select '<?xml version=''1.0'' encoding=''UTF-8''?>
<DocumentList
xmlns="http://www.xyz.com/testworkflowmodel/8.1/dms">
<documents
xmlns="">
<id>{urn:repoId:System}{uid}1e7b6e94-49c8-957128311b42</id>
<path>/temp/procinstances/2018/01/01/01/pi-7418529/example2.TIFF</path>
<name>example2.TIFF</name>
<properties>
<Member>A123456B</Member>
<Scheme>1234567890</Scheme>
<DocumentTypes>Email</DocumentTypes>
</properties>
<description></description>
<size>33127</size>
<contentType>image/tiff</contentType>
<dateCreated>2018-01-01T01:41:57</dateCreated>
<dateLastModified>2018-01-01T01:41:57</dateLastModified>
</documents>
<documents
xmlns="">
<id>{urn:repoId:System}{uid}1e7b6e94-49c8-957128311b42</id>
<path>/procinstances/2018/02/20/13/pi-7418529/procattachs/sample.tif</path>
<name>sample.tif</name>
<properties>
<Member>A123456B</Member>
<Scheme>1234567890</Scheme>
<DocumentTypes>Previous Rules</DocumentTypes>
</properties>
<description></description>
<size>10409</size>
<contentType>image/tiff</contentType>
<dateCreated>2018-02-20T13:41:57</dateCreated>
<dateLastModified>2018-02-20T13:41:57</dateLastModified>
</documents>
</DocumentList>'
as STRINGVALUE
from dual
) cd
, XMLTABLE(
xmlnamespaces(
'http://www.xyz.com/testworkflowmodel/8.1/dms' as "tns1"
, '' as "tns2"
, default 'http://www.xyz.com/testworkflowmodel/8.1/dms'
)
, '/DocumentList/documents'
PASSING XMLTYPE.createXML(cd.STRINGVALUE)
COLUMNS
id VARCHAR2(128 CHAR) path 'id',
path VARCHAR2(128 CHAR) path 'path',
name VARCHAR2(128 CHAR) path 'name',
contenttype VARCHAR2(128 CHAR) path 'contentType',
size1 NUMBER path 'size',
documenttype VARCHAR2(128 CHAR) path 'properties/DocumentType'
) xt
;