0

On Oracle 11gR2 I need to retrieve portions of an XML file and create a new one:

SELECT XMLQUERY
    ('for $e in /edi_l/trader
    where $e//pod="XX999"
    order by $e
    return <pod>{$e}</pod>'
   passing MY_T.XML_FILE
returning content
)
FROM MYTABLE MY_T;

The output:

<pod><trader cdisp="AB1111">
<idimpp num="1234">
<hdr>
<odn>567</odn>
<pod>XX999</pod>
</hdr>
...
</idimpp>
</trader>
</pod>

If I omit the tag

<pod></pod>
SELECT XMLQUERY
    ('for $e in /edi_l/trader
    where $e//pod="XX999"
    order by $e
    return {$e}
   passing MY_T.XML_FILE
returning content
)
FROM MYTABLE MY_T;

I get errors:

ORA-19114: XPST0003
LPX-00801: XQuery syntax error at '{'
4       return {$e}
-              ^
19114. 00000 -  "XPST0003 - error during parsing the XQuery expression: %s"
*Cause:    An error occurred during the parsing of the XQuery expression.
*Action:   Check the detailed error message for the possible causes.
Error at Line: 30 Column: 6

Why?

I need to retrieve the data without creating a new external tag.

1 Answer 1

2

You don't need the curly braces if you aren't embedding it in your own node:

with mytable (xml_file) as (
  select xmltype('<pod><trader cdisp="AB1111">
<idimpp num="1234">
<hdr>
<odn>567</odn>
<pod>XX999</pod>
</hdr>
</idimpp>
</trader>
</pod>')
  from dual
)
SELECT XMLQUERY
  ('for $e in //trader
    where $e//pod="XX999"
    order by $e
    return $e'
  passing MY_T.XML_FILE
  returning content
)
FROM MYTABLE MY_T;

XMLQUERY('FOR$EIN//TRADERWHERE$E//POD="XX999"ORDERBY$ERETURN$E'PASSINGMY_T.XML_FILERETURNINGCONTENT)
--------------------------------------------------------------------------------
<trader cdisp="AB1111"><idimpp num="1234"><hdr><odn>567</odn><pod>XX999</pod></hdr></idimpp></trader>

From the documentation on XQuery expressions:

Computed (dynamic) constructions – You can construct XML data at run time using computed values. For example, the following XQuery expression constructs this XML data: <foo toto="5"><bar>tata titi</bar> why? </foo>.

<foo>attribute toto {2+3},
     element bar {"tata", "titi"},
     text {" why? "}</foo>

In this example, element foo is a direct construction; the other constructions are computed. In practice, the arguments to computed constructors are not literals (such as toto and "tata"), but expressions to be evaluated (such as 2+3). Both the name and the value arguments of an element or attribute constructor can be computed. Braces ({, }) are used to mark off an XQuery expression to be evaluated.

In your first query pod is a direct construction and ${e} is computed, so the braces are necessary. In the second version you're referring directly to $e outside any construction, so there is no evaluation, and therefore no braces are required (or allowed).

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.