Xquery - Selecting value from XML using XMLtable

Question

I want to use XQuery to display the value if FrequencyCdvalue="01" and city type="first" in the following XML.

Can you pls help me here

<Envelope>
<Arrangement>
<FrequencyCd value="01">first first</FrequencyCd> 
<FrequencyCd value="02">first second</FrequencyCd> 
<contactinfo> <Address>
<street>234 Rolling Lane</street> 
<city type="first">Rockport</city> 
</Address>
<email>[email protected]</email> 
</contactinfo>
</Arrangement>
<Arrangement>
<FrequencyCd value="03">second first</FrequencyCd> 
<FrequencyCd value="04">second second</FrequencyCd> 
<contactinfo>
<Address>
<street>234 Straight Lane</street> 
<city type="first">Crackport</city> 
</Address>
<email>[email protected]</email> 
</contactinfo>
</Arrangement>
</Envelope>

What value are you trying to display? The xpath /Envelope/Arrangement[FrequencyCd/@value='01' and contactinfo/Address/city/@type='first'] should get you started. — Daniel Haley
Thanks a lot Dev - I am able to get the XPATH for this but when I am trying to put this in a Xquery (Actually we need a DB2 query) and display the values for Frequency code 01 and city type = first — user549432

Fred Sobotka Fred Sobotka · Accepted Answer · 2011-05-16T17:48:46

This approach shreds more rows internally, but allows the compound filtering criteria (the conditions for FrequencyCd and City) to be implemented as a simple WHERE clause on the SQL representation of the shredded XML data (the output of the XMLTABLE function).

with origxml(xdoc) AS (VALUES XMLPARSE( DOCUMENT 
    '<Envelope>
    <Arrangement>
    <FrequencyCd value="01">first first</FrequencyCd> 
    <FrequencyCd value="02">first second</FrequencyCd> 
    <contactinfo> <Address>
    <street>234 Rolling Lane</street> 
    <city type="first">Rockport</city> 
    </Address>
    <email>[email protected]</email> 
    </contactinfo>
    </Arrangement>
    <Arrangement>
    <FrequencyCd value="03">second first</FrequencyCd> 
    <FrequencyCd value="04">second second</FrequencyCd> 
    <contactinfo>
    <Address>
    <street>234 Straight Lane</street> 
    <city type="first">Crackport</city> 
    </Address>
    <email>[email protected]</email> 
    </contactinfo>
    </Arrangement>
    </Envelope>'
    )
)
SELECT filteredxml.FrequencyCd, filteredxml.City FROM origxml,
XMLTABLE ('$d/Envelope/Arrangement' PASSING origxml.xdoc AS "d"
                COLUMNS
                FrequencyCd     VARCHAR(20) PATH    'FrequencyCd[@value="01"]/text()',
                City            VARCHAR(30) PATH    'contactinfo/Address/city[@type="first"]/text()'
            ) as filteredxml
WHERE filteredxml.FrequencyCd IS NOT NULL
AND filteredxml.City IS NOT NULL
;

Xquery - Selecting value from XML using XMLtable

1 Answers