CODEWITHSUNDEEP
xml-parsingxqueryxquery-sql
Kumar99
Kumar99

Reputation: 11

How to extend a search for elements and values to attributes?

I have an XML document like below. I am trying to get key/value pair for all elements and attributes as well. The query I have gets all elements names and element values. However, I would like to get attribute names and attribute values too (preferably in the same query, otherwise in a different query).

XML Document:

      <bookstore>
        <book category="COOKING">
            <title lang="en">Cook Book</title>
            <author>Chef Author</author>
            <year>2015</year>
            <price>310.00</price>
        </book>
        <book category="CHILDREN">
            <title lang="en">Kid Story Book</title>
            <author>KJ Banter</author>
            <year>2010</year>
            <price>229.99</price>
        </book>
        </bookstore>

SQL Query in Oracle:

    WITH tab AS
    (SELECT xmltype('
                 <bookstore>
        <book category="COOKING">
            <title lang="en">Cook Book</title>
            <author>Chef Author</author>
            <year>2015</year>
            <price>310.00</price>
        </book>
        <book category="CHILDREN">
            <title lang="en">Kid Story Book</title>
            <author>KJ Banter</author>
            <year>2010</year>
            <price>229.99</price>
        </book>
        </bookstore>
    ') col
    FROM dual
    )
    SELECT nodepath,nodevalue
      FROM tab t,
       xmltable('
        for $i in $tmp/descendant::*
        where $i/text() != ""
        return <R><P>{string-join($i/ancestor-or-self::*/name(), "/")}</P><V>{$i/text()}</V></R>' 
        passing t.col AS "tmp" columns
        nodepath varchar2(1000) path '//P',
        nodevalue varchar2(1000) path '//V')

Upvotes: 1

Views: 118

Answers (1)

Jens Erat
Jens Erat

Reputation: 38682

Given you want them in document order, descend for each retrieved element and follow its self- and attribute axis. You need to retrieve element and attribute values differently, so apply a typeswitch:

for $node in /descendant::*/(., @*)
let $value := (
  typeswitch ($node)
  case element() return $node/text()
  case attribute() return data($node)
  default return ()
)
where $value
return <R>
  <P>{
    string-join(($node/name(), $node/ancestor::*/name()), "/")
  }</P>
  <V>{ $value }</V>
</R>

Also observe I changed the ancestor-or-self step to an ancestor step and explicitly return $node's name, so we don't have to distinguish between elements and attributes again in the ancestor axis.

Upvotes: 1

Related Questions