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
– Daniel Haley, Commented May 14, 2011 at 6:20
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
– user549432, Commented May 16, 2011 at 14:50

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

2

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
;

answered May 16, 2011 at 17:48

Fred Sobotka

5,33225 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user549432 Over a year ago

Thanks a lot Fred - This worked - Also I have one more requirement where in I should use // (i.e whereever it exists in the XML) i.e use // Arrangement/FrequencyCd[@value="01"] but when I try that I am getting an error.

user549432 Over a year ago

Thanks a lot Fred - This worked - Also I have one more requirement where in I should use // (i.e whereever it exists in the XML) i.e use // Arrangement/FrequencyCd[@value="01"] but when I try that I am getting an error. - I also tried removing '$d/Envelope/Arrangement' - Apprecite your help SELECT filteredxml.FrequencyCd, filteredxml.City FROM testxml,XMLTABLE ('$d/Envelope/Arrangement' PASSING testxml.contact AS "d" COLUMNS FrequencyCd VARCHAR(20) PATH '//FrequencyCd[@value="02"]/text()',City VARCHAR(30) PATH '//contactinfo/Address/city[@type="first"]/text()') as filteredxml

user549432 Over a year ago

This is a sample XML and my actual requirement is to work on a bigger xml - I want to know is there a way I can refer to different paths in the same Xquery like //ABC/DEF/GHI and ??MNO/PQR/STU

Fred Sobotka Over a year ago

XMLTABLE needs to know what your row-producing expression is in order to work. If your XML document is not organized in a way that makes sense as a table, you won't be able to get the results you want in a single XMLTABLE call. In those situations, you could join more than one XMLTABLE expression together, or run the document through XSLTRANSFORM to radically reorganize it in order to make it work with one XMLTABLE expression.

Collectives™ on Stack Overflow

Xquery - Selecting value from XML using XMLtable

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related