XQuery to retrieve values from child nodes of an element node having name changes for every XML record

Question

XML Response payload as below

<root>
 <_1>
  <expand>description,lead,url,projectKeys</expand>
  <name>ABC</name>
  <id>12631</id>
</_1>
<_2>
  <expand>description,lead,url,projectKeys</expand>
  <name>XYZ</name>
  <id>12632</id>
</_2>
<_3>
  <expand>description,lead,url,projectKeys</expand>
  <name>JKL</name>
  <id>12634</id>
 </_3>
</root>

Here <_1>,<_2>,<_3> can increase sequentially depends on total count of XML records.

My requirements are to write an XQuery

To identify total count of values in Element nodes (For eg:- 50 i.e last node is <_50>)
Iterate thru all element nodes till 50 (for instance) and pull its corresponding child nodes (for example : expand,name,id)

I had a similar XML response payload, where instead of sequential increase in the element node values (<_1>,<_2>,<_3> ) , it has a fixed name (entries) to all element nodes . (Example below) and I wrote an XQuery to retrieve the values from its child nodes (see below).
```
 <root>
  <entries>
  <added>1534815831000</added>
  <updated>1534815831000</updated>
  </entries>
 <entries>
  <added>1534815832000</added>
  <updated>1534815832000</updated>
  </entries>
  </root>
```

XQuery was

 let $entries := /root/entries
  return
  for $entry in $entries
  return
    <entries>
     {
    <added>{data($entry/added)}</added>,
    <updated>{data($entry/updated)}</updated>
     }
    </entries>

Challenges are

a) When the element node name changes with every record

b)Iteration through all element nodes

Any valuable suggestions are much appreciated.

Thanks

It is not clear what the desired output is. Or are you just having trouble limiting to the first 50 elements when the element names are all different? — Mads Hansen, Aug 29 '18 at 01:57

Mads Hansen · Answer 1 · 2018-08-29T11:34:32.847

0

You could select the first 50 elements with a predicate filter: /root/*[1 to 50]

You can also use a positional variable when iterating through the set of entry elements, a where clause in your FLWOR to restrict to the first 50, and copy all of the $entry/* child elements instead of reconstructing them:

for $entry at $i in /root/*
where $i <= 50
return
 element { $entry/local-name() } { 
   $entry/name 
 }

edited Aug 29 '18 at 11:34

answered Aug 29 '18 at 01:37

Mads Hansen

63,927
12
112
147

Desired output is <_1>ABC Likewise for <_2> ,<_3>...till the end limit; it should be in an iterative manner. End limit should also be determined. – Tom George Aug 29 '18 at 03:19

score 0 · Answer 2 · answered Aug 29 '18 at 07:33

0

Getting the count of entries is just count(/root/*).

Copying the first 50 entries is just subsequence(/root/*, 1, 50).

Copying the first 50 entries while retaining only the name child element is

for $e in subsequence(/root/*, 1, 50)
return element{local-name($e)}{$e/name}

answered Aug 29 '18 at 07:33

Michael Kay

156,231
11
92
164

It works. However , as name and id are also child elements of the same parent element node , I need to retain that as well. For that , I wrote that Xquery which returns (all child values of 'name' then all child values of 'id') and it's not an expected result. Desired output as first child value of 'name' then first child value of 'id'.2nd child value of 'name' then 2nd child value of 'id'. X Query is : for $entry at $i in /root/* return element{local-name($entry)}{$entry/name}, for $entry at $i in /root/* return element{local-name($entry)}{$entry/self} – Tom George Aug 29 '18 at 22:57
This kind of transformation, where you retain most of the data from the source document and make a few changes, is actually much easier to do in XSLT than XQuery. Alternatively, consider XQuery Update. – Michael Kay Aug 30 '18 at 07:42

XQuery to retrieve values from child nodes of an element node having name changes for every XML record

2 Answers2