CODEWITHSUNDEEP
oracle-databasexquery
jhon.smith
jhon.smith

Reputation: 2043

Xquery does not written a result

I have the following Xquery

  select email1
  from customers,
  XMLTABLE(
                   '$customer/customerinfo/contacts/phone[@type="work"]'
                   PASSING object_value as "customer"
                   columns
                   email1 varchar2(60) path '/emails/email1'
           ) as x
EMAIL1
------------------------------------------------------------


1 row selected.

When executed on a table of customers of xmltype stored in oracle 12c i do not get any result but a blank . The xml itself looks something like this

<customerinfo xmlns:ns0="http://posample.org" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" Cid="1000">
<name>Kathy Smith</name>
<addr country="Canada">
<street>5 Rosewood</street>
<city>Toronto</city>
<prov-state>Ontario</prov-state>
<pcode-zip>M6W 1E6</pcode-zip>
</addr>
<contacts>
<phone type="work">416-555-1358</phone>
    <emails>
    <email1>[email protected]</email1>
    <email2>[email protected]</email2>
    </emails>
<phone type="personal">416-555-1358</phone>
    <emails>
    <email1>[email protected]</email1>
    <email2>[email protected]</email2>
    </emails>
</contacts>
</customerinfo>

1.I want the output to be [email protected].

Upvotes: 1

Views: 42

Answers (1)

Jens Erat
Jens Erat

Reputation: 38682

<emails1/> is no children of <phone/>. This XML format is a little bit broken, as you cannot directly select any "work" email address.

An XPath expression which only matches the first <email1/> node after the "work phone" would be

/customerinfo/contacts/phone[@type="work"]/following-sibling::email1[1]

Upvotes: 1

Related Questions