Reading tags in an XML CLOB column-CodePudding

I've searched several posts here and tried different approaches. I don't get errors but I also don't get output. In our system we store incoming xml transactions in a CLOB column L80T2.tridata, now users want to get warned when a new transaction comes so that they can check if all important fields in the DB are populated.

In the example I would like to have as output the PartId i.e. <Id>R31312/CL7C</Id>

Here is the full xml

<MaXML_Envelope version="0140"xsi:schemaLocation="http://www.xxx.kom/Distro ../../../../mastd/xsd/MaXMLDoc/0140_PartUpdate_0100.xsd ">
<PartUpdate version="0100">
    <ControlArea>
        <Sender>
            <Division>A1</Division>
            <Confirmation>0</Confirmation>
        </Sender>
        <CreationDateTime>2022-02-15T00:00:00Z</CreationDateTime>
        <RefId>
            <ReferenceId>90000206</ReferenceId>
        </RefId>
        <TransactionType>LP04</TransactionType>
        <SenderId>TRNM</SenderId>
        <ReceiverId>FMHW</ReceiverId>
    </ControlArea>
    <DataArea>
        <DataBaseTransaction sequence="1">
            <PartUpdate>
                <PartId>
===>                 <Id>R31312/CL7C</Id>
                     <Revision>9105</Revision>
                     <Division>A1</Division>
                 </PartId>
                 <Description>1606058</Description>
                 <UnitType/>
                 <PartToleranceArea>
                     <Inspection>N</Inspection>
                 </PartToleranceArea>
                 <UserArea>
                     <Wadmkey/>
                     <Description>1606058</Description>
                     <LanguageDescr_Segment sequence="1">
                         <DescrLanguage>EN</DescrLanguage>
                         <Description>1606058</Description>
                     </LanguageDescr_Segment>
                     <PartType>C</PartType>
                     <PartWeight>0.0</PartWeight>
                     <PartWeightUnit>KGM</PartWeightUnit>
                     <SingleUnitInstance/>
                     <HandlingUnitInstance/>
                     <PackCode/>
                     <ComCode/>
                 </UserArea>
            </PartUpdate>
        </DataBaseTransaction>
    </DataArea>
</PartUpdate>
</MaXML_Envelope>

I have done so far the following query (sqlplus)

SELECT XMLQUERY(
    'declare default element namespace "http://www.xxx.kom/Distro ../../../../mastd/xsd/MaXMLDoc/0140_PartUpdate_0100.xsd"; (: :)
    /PartUpdate/DataArea/DataBaseTransaction/PartId/Id/text()'     
    PASSING XMLTYPE(L80T2.tridata)
    RETURNING CONTENT
) myxmldata,  L79T1.trno, L79T1.comtype
from L79T1
inner join L80T2 on (L80T2.trno = L79T1.trno)
where L79T1.trtype = '702'

and here is the output of above query

MYXMLDATA
------------------
      TRNO COMT
---------- ----

 161657423 MQS


 161672521 MQS


 161666181 MQS

What am I doing wrong?

I'm expecting something like:

 MYXMLDATA        TRNO COMT
 ----------- --------- ----
 R31312/CL7C 161657423 MQS
 V34132/AB   161672521 MQS
 OR-12BC     161666181 MQS

Thanks for any help.

CodePudding user response：

Your path is missing the root MaXML_Envelope node, and the intermediate PartUpdate node.

So this:

/PartUpdate/DataArea/DataBaseTransaction/PartId/Id/text()

should be:

/MaXML_Envelope/PartUpdate/DataArea/DataBaseTransaction/PartUpdate/PartId/Id/text()

You also don't need to declare a default namespace, at least with what you've shown; but your XML should be declaring the xsi namespace (or must be, or you wouldn't see the result you do now; so you've lost that in posting?).

And you might also want to call getStringVal() to get a varchar2 result.

In full:

SELECT XMLQUERY(
    '/MaXML_Envelope/PartUpdate/DataArea/DataBaseTransaction/PartUpdate/PartId/Id/text()'     
    PASSING XMLTYPE(L80T2.tridata)
    RETURNING CONTENT
).getStringVal() myxmldata,  L79T1.trno, L79T1.comtype
from L79T1
inner join L80T2 on (L80T2.trno = L79T1.trno)
where L79T1.trtype = '702'

db<>fiddle