Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Amit.

Asked: May 23, 2018 - 7:22 pm UTC

Last updated: May 28, 2018 - 1:35 am UTC

Version: 12.1

Viewed 1000+ times

You Asked

Hi Tom,
I am trying to learn XQuery use with SQL but it looks very complicated.
Can you please advise with some simple cases (I am not interested in XML generation but using XML and XQuery functions with relational data tables).
Where to start? What are advantages of learning it and how it can benefit.

Example and solution: https://community.oracle.com/thread/668829?start=0&tstart=0

select * from
   xmltable('declare function local:reverse($a)
             {
               if (string-length($a) != 0) then
                concat(substring($a,string-length($a),1), local:reverse(substring($a,1,string-length($a)-1)))
               else ()
             }; (: eof :)
             declare function local:sum($a)
             {
               if (string-length($a) != 0) then
                 xs:integer(substring($a,1,1)) + xs:integer(local:sum(substring($a,2)))
               else (0)
             }; (: eof :)
             declare function local:prod($a)
             {
               if (string-length($a) != 0) then
                 xs:integer(substring($a,1,1)) * xs:integer(local:prod(substring($a,2)))
               else (1)
             }; (: eof :)
             for $i in 1 to 10000
              where $i - local:reverse(xs:string($i)) = local:sum(xs:string($i)) + local:prod(xs:string($i))
                    return $i' columns x integer path '.');

and Connor said...

Xquery is a standard for interrogating XML documents. So the relationship with SQL is minor - in the example above, there is actually NO relationship with SQL. All that is being done is a standalone Xquery is being run, and then the XMLTABLE is purely to get the result back to the screen.

So Xquery isnt "ours" (ie, Oracle) as such - and there are plenty of resources out there to learn it (like any other language), eg

https://www.w3schools.com/xml/xquery_intro.asp


Rating

  (1 rating)

Comments are currently disabled for this question.

Comments

Not useful reply

Amit Saroha, May 24, 2018 - 11:05 am UTC

Hi
Looks like you are over loaded and didn’t think thoroughly while replying. You didn’t realize what I asked.
I never asked to learn XQuery but how to use it with sql like it was used in example posted.
I just want to check out different ways we can use it with sql.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.