ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence on multiple rows with same name

Question

I'm trying to parse a to xml converted SOAP request response. Please notice that this is my first time using this kind of query. It has the following structure:

<soap:Body>
    <ns2:findDocumentsResponse xmlns:ns2="http://www.datengut.de">
        <ecmDocumentInfo>
            <id>53CA3873CCFCC44FB5FE99047E5ED04E000000000000</id>
            <fields>
                <entry>
                    <key>SYSFILENAMES</key>
                    <value>
                        <name>SYSFILENAMES</name>
                        <type>STRING</type>
                        <values xsi:type="xs:string" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xs="http://www.w3.org/2001/XMLSchema">test_file1.pdf</values>
                        <values xsi:type="xs:string" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xs="http://www.w3.org/2001/XMLSchema">test_file2.pdf</values>
                        <values xsi:type="xs:string" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xs="http://www.w3.org/2001/XMLSchema">test_file3.pdf</values>
                        <values xsi:type="xs:string" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xs="http://www.w3.org/2001/XMLSchema">test_file4.pdf</values>
                        <isMulti>true</isMulti>
                    </value>
                </entry>
            </fields>
    ....

The SQL Query I'm using is the following:

SELECT 
    t."key", f."value"
FROM
    XMLTABLE(
        xmlnamespaces('http://schemas.xmlsoap.org/soap/envelope/' as "soap",'http://www.datengut.de' as "ns2"),
        '/soap:Envelope/soap:Body/ns2:findDocumentsResponse/ecmDocumentInfo/fields/entry'
    PASSING xmltype.createXML('[see structure above]') 
    COLUMNS
        "key" varchar2(4000) PATH 'key',
        "path" XMLType path 'value'
    ) t
    CROSS JOIN XMLTABLE(
        'value'
        PASSING t."path"
        COLUMNS
            "value" varchar2(4000) PATH '.'
    ) f

The Output I'm receiving:

KEY                 VALUE
------------------- ---------------------------------------
SYSFILENAMES        SYSFILENAMESSTRINGtest_file1.pdftest_file2.pdftest_file3.pdftest_file4.pdftrue

The Output I'm expecting to receive:

KEY                 VALUE
------------------- ---------------------------------------
SYSFILENAMES        test_file1.pdf
SYSFILENAMES        test_file2.pdf
SYSFILENAMES        test_file3.pdf
SYSFILENAMES        test_file4.pdf

When I change the line:

"value" varchar2(4000) PATH '.'

to

"value" varchar2(4000) PATH 'values'

I receive the "ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence" error message. The error does not appear when PATH is '.' but then I receive every value of the value entry in the same line. I just want to display the filenames like shown in the expected result section. How do I cross join the second XMLTABLE f to receive my expected output. Many thanks in advance.

Alex Poole · Answer 1 · 2021-08-06T10:11:49.077

You can just add /values to your second XPath:

    CROSS JOIN XMLTABLE(
        'value/values'
        PASSING t."path"
        COLUMNS
            "value" varchar2(4000) PATH '.'
    ) f

key	value
SYSFILENAMES	test_file1.pdf
SYSFILENAMES	test_file2.pdf
SYSFILENAMES	test_file3.pdf
SYSFILENAMES	test_file4.pdf

You can also do it in one XMLTable call, by finding the values first and walking back up the tree to get the key (which doesn't work properly in 11.2.0.2 or 11.2.0.3, but does in later versions):

SELECT 
    t."key", t."value"
FROM
    XMLTABLE(
        xmlnamespaces('http://schemas.xmlsoap.org/soap/envelope/' as "soap",'http://www.datengut.de' as "ns2"),
        '/soap:Envelope/soap:Body/ns2:findDocumentsResponse/ecmDocumentInfo/fields/entry/value/values'
    PASSING xmltype.createXML('[see structure above]') 
    COLUMNS
        "key" varchar2(4000) PATH './../../key',
        "value" varchar2(4000) path '.'
    ) t

db<>fiddle with completed XML (added closing tags, and soap:Envelope wrapper with namespace declaration).

If you might have, and want to report, keys without values then you could go back to the two-XMLTable version but use outer apply instead of cross join; but that isn't available until 12c.

Incidentally, you don't have to explicitly use createXML', you can just do PASSING xmltype('[see structure above]'). And I would avoid quoted identifiers if at all possible; if they really have to be lower-case then I'd leave them unquoted throughout the query and just add a quoted alias in the final outer select list.

As long as there's 11g tag, `outer apply` is not an option. And interesting part is that [your own comment](https://stackoverflow.com/questions/51361150/oracle-xmltable-fetching-column-from-parent-node#comment89696445_51361554) about access to parent nodes is: *This works in 11.2.0.4, 12.1.0.2 and 12.2.0.1. But it does get null in 11.2.0.2 (db<>fiddle), which I guess is a bug in that version* — astentx, Aug 06 '21 at 09:52
Yes, I missed that tag; and walking up the tree doesn't work properly in 11.2.0.2, it's fixed later. — Alex Poole, Aug 06 '21 at 10:09

score 1 · Accepted Answer · answered Aug 06 '21 at 09:43

It worked with:

SELECT 
    t."key", f."value"
FROM
    XMLTABLE(
        xmlnamespaces('http://schemas.xmlsoap.org/soap/envelope/' as "soap",'http://www.datengut.de' as "ns2"),
        '/soap:Envelope/soap:Body/ns2:findDocumentsResponse/ecmDocumentInfo/fields/entry/value'
    PASSING xmltype.createXML('[see structure above]') 
    COLUMNS
        "key" varchar2(4000) PATH 'name',
        "path" XMLType path 'values'
    ) t
    CROSS JOIN XMLTABLE(
        'values'
        PASSING t."path"
        COLUMNS
            "value" varchar2(4000) PATH '.'
    ) f

ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence on multiple rows with same name

2 Answers2