Help with xmltable - tell me there is a better way of doing this?

[Vectorspace]

I have this XML, that I am trying to decode with XMLTABLE

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
    <soap:Body>
        <InteractiveResponse xmlns="http://www.foo.com/bar/foo/bar/">
            <OutputRoot>
                <Output>
                    <CommercialData>
                        <Identifiers>
                            <pHCompany>gbr100001046</pHCompany>
                        </Identifiers>
                    </CommercialData>
                </Output>
            </OutputRoot>
        </InteractiveResponse>
    </soap:Body>
</soap:Envelope>

Note this is simplified, CommercialData actually contains dozens of fields

The fact that InteractiveResponse has a namespace declaration without including it in the tag name is messing things up for me. I assumed that the XPATH expression for getting to PhCompany would simply be:

/*:Envelope/*:Body/InteractiveResponse/OutputRoot/Output/CommercialData/Identifiers/pHCompany

But that returned nothing.

After much googling, I found that this would work:

/*:Envelope/*:Body/*[local-name()='InteractiveResponse']/*[local-name()='OutputRoot']/*[local-name()='Output']/*[local-name()='CommercialData']/*[local-name()='Identifiers']/*[local-name()='pHCompany']

Which means for this one field, my XMLTABLE statement looks like this:

select pHCompany from MYFILE f1,
xmltable('/*:Envelope/*:Body/*[local-name()=''InteractiveResponse'']/*[local-name()=''OutputRoot'']
         /*[local-name()=''Output'']/*[local-name()=''CommercialData'']'
         passing XMLIN columns
   pHCompany varchar(15) path '*[local-name()=''Identifiers'']
                              /*[local-name()=''pHCompany'']') as commercial

The paths are huge and cumbersome. Is there a better way?

[JonBoy]

Looks to be a trivial job for XML-INTO but I'm sure somebody will come up with a way to make the SQL at least tolerable readable.

[Vectorspace]

I thought about XML-INTO, but XMLTABLE would be my preference in this case as it lets us pick and choose which fields we want.
(plus building the nested DS's for this would be a huge pain)

[JonBoy]

OK - it seemed like you only wanted the content of CommercialData and even with multiple fields/nesting levels is it that hard?

Are you are aware that you can do this ?

dcl-ds A;
F1A;
dcl-ds B;
F1B;
dcl-ds C;
F1C;
end-ds;
end-ds;
end-ds;