Return element name and value as key value pair

Question

I am working on a requirement where I need to return element name and value as Key value pair as below using XQUERY.

[code=123,px_last=value attribute of first data,last_update=value attribute of 2nd data and so on]

There are 7 data element with attribute value inside should be read as above with first field mapped to first data and second mapped to second data value attribute. etc..

With your help I was able to generate the output but got struck where I need to map the first field element to first data attribute value and so on etc.

Thanks in advance

XML file:

<root>
<fields>
    <field>PX_LAST</field>
    <field>LAST_UPDATE</field>
    <field>LAST_UPDATE_DT</field>
    <field>SECURITY_DES</field>
    <field>FUT_CUR_GEN_TICKER</field>
    <field>YLD_CNV_BID</field>
    <field>YLD_CNV_ASK</field>
</fields>
<Datas>
    <Data>
        <code>0</code>
        <ins>
            <id>CT30</id>
            <key>Govt</key>
        <ins/>
        <data value="98.843750"/>
        <data value="16:14:45">
        </data>
        <data value="06/03/2014"/>
        <data value="T 3 3/8 05/15/44"/>
        <data value=""/>
        <data value="3.439"/>
        <data value="3.437"/>
    </Data>
    <Data>
        <code>0</code>
        <ins>
            <id>US0001W</id>
            <key>Index</key>
        <ins/>
        <data value=".119000"/>
        <data value="06:46"/>
        <data value="06/03/2014"/>
        <data value="ICE LIBOR USD 1 Week"/>
        <data value=""/>
        <data value="N.A."/>
        <datavalue=".11900"></data>
    </Data>
</Datas>
</root>

XQuery:

declare function xf:strip-namespace($e as element())
as element()
{
element { xs:QName(local-name($e)) }
{
  for $child in $e/(@*,node())
   return
 if ($child instance of element())
 then
   xf:strip-namespace($child)
 else 
   $child
 }
};

let $nl := "
"

let $count := 0

for $x in       doc("test.xml")/soap:Envelope/soap:Body/dlws:retrieveGetDataResponse/dlws:instrumentDatas//*
let $y:=xf:strip-namespace($x)
return
if($y/name() = 'instrumentData')
then
concat($nl,'[','')
else if($y/name()='data')
then
  concat($y/name(),'=',$y/data(@value),',')
else if($y/name() != 'instrument')
then
  concat($y/name(),'=',$y/text(),',')
else
()

Output right now:

[code=123,data=werr,data="qwe",data="wer",......,] [code=456,data=rty,data="tyuu",data="uuu",......,]

Answer 1

Generally, if you can break your problem into smaller pieces, it will facilitate a simpler solution that more closely resembles the problem itself.

declare function local:make-pair(
  $e as element()
) as xs:string?
{
  typeswitch($e)
    case element(data) return concat(local-name($e), '=', $e/@value)
    default return concat(local-name($e), '=', $e)
};

let $idatas :=
<idatas>
 <idata>
    <code>123</code>
    <data value="wer"></data>
    <data value="sdf"></data>
    <data value="zxc"></data>
    <data value="asd"></data>
    <data value="jgh"></data>
    <data value="cvb"></data>
    <data value="bsz"></data>
 </idata>

 <idata>
    <code>345</code>
    <data value="ff"></data>
    <data value="zxd"></data>
    <data value="wvver"></data>
    <data value="wencvr"></data>
    <data value="wzxcer"></data>
    <data value="wmmer"></data>
    <data value="wuuer"></data>
 </idata>
</idatas>
for $idata in $idatas/idata
let $pairs := 
  for $p in $idata/*
  return local:make-pair($p)
return concat('[', string-join($pairs, ','), ']')

Answer 2

In the following parts of the answer, I completely ignored the strip-namespace part, which is a bad idea anyway. Eighter declare it as default namespace and don't worry about it any more, or use local-name() instead of name, or use the wildcard namespace mather*:elementname*.

---

The input was modified during updates of the question. Everything up to the next horizontal bar refers to the first revision of the question.

You can do all the "string manipulation foo" with very few code using some XQuery 3.0 features, especially calling functions in axis steps and the string concatenation operator ||:

//idata/(                      (: for all idata elements :)
  "[" ||
  string-join((                (: combine all key/value pairs with commata :)
    "code=" || code/data(),    (: code header :)
    data/("data=" || @value)), (: data fields :)
  ',') ||
  ']')

And it exactly fits into one line on Stack Overflow (if you really want it)!

//idata/("["||string-join(("code="||code/data(),data/("data="||@value)),',')||']')

With the output being

[code=123,data=wer,data=sdf,data=zxc,data=asd,data=jgh,data=cvb,data=bsz] [code=345,data=ff,data=zxd,data=wvver,data=wencvr,data=wzxcer,data=wmmer,data=wuuer]

A probably more readable version with explicit loops, still using the concatenation operator (which in my opinion enhances readability):

for $idata in $xml//idata
return
  "[" || string-join((
    "code=" || $idata/code/data(),
    for $data in $idata/data
    return
      "data=" || $data/@value),
  ',') || ']'

---

For the updated question, a one-liner will probably get too unreadable. The modified code in the end just joins with the index of the data element:

for $dataset in /root/Datas/Data
return
  "[" || string-join((
    "code=" || $dataset/code/data(),
    for $data at $position in $dataset/data 
    let $field := /root/fields/field[$position]
    return
      $field || "=" || $data/@value),
  ',') || ']'