XML element dynamic in xquery

Question

My text file is EDI format:

    UNB+UNOA:1+5013546126405::CCC+5016237006116+160729:1253+00952177'UNH+02585706+ORDCHG:D:99B:UN'BGM+230:::Purchase Order Change V201+1911677::0+5+AB'DTM+137:201607291253:203'DTM+4:201606150000:203'DTM+154:201608080000:203'RFF+CT:XXXX/0140'NAD+VN++XXX HOUSE:XXXX CLOSE::XXX+XX XX XX XX LIMITED++XXX+:162::United Kingdom+XX 0XX'LOC+80+:::U3F36'CTA+CW'COM+N/A:TE'NAD+ST++9999++++:162::United Kingdom'NAD+BT++XX XX IP:XX HOUSE,:XX XX,:XX+++XX,+:162::United Kingdom+XX XXX'LOC+2+:::XXXXX'NAD+BY++XX XX XX:XX HOUSE:XX XX:XX+++XX+:162::UnitedKingdom+XXX XXXX'LOC+158+:::XXXXXX'RFF+VA'CTA+OC+:XX XX'COM+01593424302:TE'CTA+PD+:POWELL, WO(1,2) XXX'COM+01600 711338:TE'CUX+2:GBP'PAT+ZZZ+7:::Immediate'LIN+1++:VN'PIA+1+1301W42151600:CG+::184+Y::52+:VN'PIA+5+1117394:AU'IMD+F++:::Plaster Self Adhesive Blue Assorted 100/Pk'IMD+C++:20'MEA+ABA+:::EACH'QTY+21:3:EA'QTY+169:3:EA'QTY+182:0:EA'QTY+194:0:EA'QTY+256:0:EA'DTM+2:201606170000:203'MOA+128:30.54'MOA+146:10.18'FTX+LIN'RFF+ON:1911677'RFF+FI:1/1'RFF+ABO:1911677/1/1'RFF+ADE:PKE00'NAD+ST++XX XX XX XX:XX XX, XX XX,:XXX+++XXXX+:162::United Kingdom+XXX XXX'LOC+8+:::A3101A'CTA+PD+:Requestor on Header'UNS+S'UNT+46+02585706'UNH+02585706+ORDCHG:D:99B:UN'BGM+230:::Purchase Order ChangeV201+1911677::0+5+AB'DTM+137:201607291253:203'DTM+4:201606150000:203'DTM+154:201608080000:203'RFF+CT:ZZZZ/0140'NAD+VN++ZZZ HOUSE:ZZZZ CLOSE::ZZZ+ZZ ZZ ZZ ZZ LIMITED++ZZZ+:162::United States+ZZ 0ZZ'LOC+80+:::U3F36'CTA+CW'COM+N/A:TE'NAD+ST++9999++++:162::United Kingdom'NAD+BT++ZZ ZZ IP:ZZ HOUSE,:ZZ ZZ,:ZZ+++ZZ,+:162::United Kingdom+ZZ ZZZ'LOC+2+:::ZZZZZ'NAD+BY++ZZ ZZ ZZ:ZZ HOUSE:ZZ ZZ:ZZ+++ZZ+:162::UnitedKingdom+ZZZ ZZZZ'LOC+158+:::ZZZZZZ'RFF+VA'CTA+OC+:ZZ ZZ'COM+01593424302:TE'CTA+PD+:POWELL, WO(1,2) ZZZ'COM+01600 711338:TE'CUZ+2:GBP'PAT+ZZZ+7:::Immediate'LIN+1++:VN'PIA+1+1301W42151600:CG+::184+Y::52+:VN'PIA+5+1117394:AU'IMD+F++:::Plaster Self Adhesive Blue Assorted 100/Pk'IMD+C++:20'MEA+ABA+:::EACH'QTY+21:3:EA'QTY+169:3:EA'QTY+182:0:EA'QTY+194:0:EA'QTY+256:0:EA'DTM+2:201606170000:203'MOA+128:30.54'MOA+146:10.18'FTZ+LIN'RFF+ON:1911677'RFF+FI:1/1'RFF+ABO:1911677/1/1'RFF+ADE:PKE000'NAD+ST++ZZ ZZ ZZ ZZ:ZZ ZZ, ZZ ZZ,:ZZZ+++ZZZZ+:162::United Kingdom+ZZZ ZZZ'LOC+8+:::A3101A'CTA+PD+:Requestor on Header'UNS+S'UNT+46+02585706'UNZ+1+00952177'

I want to get the result from xquery as below:

    <Segment>
      <UNB>UNB+UNOA:1+5013546126405::CCC+5016237006116+160729:1253+00952177</UNB>
      <UNH>UNH+02585706+ORDCHG:D:99B:UN</UNH>
      <BGM>BGM+230:::Purchase Order Change V201+1911677::0+5+AB</BGM>
      <DTM>DTM+137:201607291253:203</DTM>
      <DTM>DTM+4:201606150000:203</DTM>
      <DTM>DTM+154:201608080000:203</DTM>
      <RFF>RFF+CT:XXXX/0140</RFF>
      <NAD>NAD+VN++XXX HOUSE:XXXX CLOSE::XXX+XX XX XX XX LIMITED++XXX+:162::United Kingdom+XX 0XX</NAD>
      <LOC>LOC+80+:::U3F36</LOC>

I am trying to write xquery but it is giving me error, could anyone please advice me how to resolve the error:

    <Segment>{ for $id in fn:tokenize(fn:substring-before(fn:substring-after($body,""),"UNZ"),"'")
     let $x := fn:substring-before(fn:substring-after($id,""),"+")
     return
     element {$x} {$id} }
    </Segment>

error:

    Error executing the transformation: Error parsing XML: {err}XP0021: "": can not cast to {http://www.w3.org/2001/XMLSchema}QName: error: Invalid QName value: Localname not a valid NCName in ''

I tried in two steps, stored below result in variable

    <Segment>{ for $id in fn:tokenize(fn:substring-before(fn:substring-after($body,""),"UNZ"),"'")
     return
     <token>{$id}</token> }
    </Segment>

The ouput of variable will be

    <Segment>
<token>
UNB+UNOA:1+5013546126405::CCC+5016237006116+160729:1253+00952177
</token>
<token>UNH+02585706+ORDCHG:D:99B:UN</token>
<token>
BGM+230:::Purchase Order Change V201+1911677::0+5+AB
</token>
<token>DTM+137:201607291253:203</token>
<token>DTM+4:201606150000:203</token>
<token>DTM+154:201608080000:203</token>
<token>RFF+CT:XXXX/0140</token>
<token>
NAD+VN++XXX HOUSE:XXXX CLOSE::XXX+XX XX XX XX LIMITED++XXX+:162::United Kingdom+XX 0XX
</token>
<token>LOC+80+:::U3F36</token>
<token>CTA+CW</token>
<token>COM+N/A:TE</token>
<token>NAD+ST++9999++++:162::United Kingdom</token>
<token>
NAD+BT++XX XX IP:XX HOUSE,:XX XX,:XX+++XX,+:162::United Kingdom+XX XXX
</token>
<token>LOC+2+:::XXXXX</token>
<token>
NAD+BY++XX XX XX:XX HOUSE:XX XX:XX+++XX+:162::UnitedKingdom+XXX XXXX
</token>
<token>LOC+158+:::XXXXXX</token>
<token>RFF+VA</token>
<token>CTA+OC+:XX XX</token>
<token>COM+01593424302:TE</token>
<token>CTA+PD+:POWELL, WO(1,2) XXX</token>
<token>COM+01600 711338:TE</token>
<token>CUX+2:GBP</token>
<token>PAT+ZZZ+7:::Immediate</token>
<token>LIN+1++:VN</token>
<token>PIA+1+1301W42151600:CG+::184+Y::52+:VN</token>
<token>PIA+5+1117394:AU</token>
<token>
IMD+F++:::Plaster Self Adhesive Blue Assorted 100/Pk
</token>
<token>IMD+C++:20</token>
<token>MEA+ABA+:::EACH</token>
<token>QTY+21:3:EA</token>
<token>QTY+169:3:EA</token>
<token>QTY+182:0:EA</token>
<token>QTY+194:0:EA</token>
<token>QTY+256:0:EA</token>
<token>DTM+2:201606170000:203</token>
<token>MOA+128:30.54</token>
<token>MOA+146:10.18</token>
<token>FTX+LIN</token>
<token>RFF+ON:1911677</token>
<token>RFF+FI:1/1</token>
<token>RFF+ABO:1911677/1/1</token>
<token>RFF+ADE:PKE00</token>
<token>
NAD+ST++XX XX XX XX:XX XX, XX XX,:XXX+++XXXX+:162::United Kingdom+XXX XXX
</token>
<token>LOC+8+:::A3101A</token>
<token>CTA+PD+:Requestor on Header</token>
<token>UNS+S</token>
<token>UNT+46+02585706</token>
<token>UNH+02585706+ORDCHG:D:99B:UN</token>
<token>
BGM+230:::Purchase Order ChangeV201+1911677::0+5+AB
</token>
<token>DTM+137:201607291253:203</token>
<token>DTM+4:201606150000:203</token>
<token>DTM+154:201608080000:203</token>
<token>RFF+CT:ZZZZ/0140</token>
<token>
NAD+VN++ZZZ HOUSE:ZZZZ CLOSE::ZZZ+ZZ ZZ ZZ ZZ LIMITED++ZZZ+:162::United States+ZZ 0ZZ
</token>
<token>LOC+80+:::U3F36</token>
<token>CTA+CW</token>
<token>COM+N/A:TE</token>
<token>NAD+ST++9999++++:162::United Kingdom</token>
<token>
NAD+BT++ZZ ZZ IP:ZZ HOUSE,:ZZ ZZ,:ZZ+++ZZ,+:162::United Kingdom+ZZ ZZZ
</token>
<token>LOC+2+:::ZZZZZ</token>
<token>
NAD+BY++ZZ ZZ ZZ:ZZ HOUSE:ZZ ZZ:ZZ+++ZZ+:162::UnitedKingdom+ZZZ ZZZZ
</token>
<token>LOC+158+:::ZZZZZZ</token>
<token>RFF+VA</token>
<token>CTA+OC+:ZZ ZZ</token>
<token>COM+01593424302:TE</token>
<token>CTA+PD+:POWELL, WO(1,2) ZZZ</token>
<token>COM+01600 711338:TE</token>
<token>CUZ+2:GBP</token>
<token>PAT+ZZZ+7:::Immediate</token>
<token>LIN+1++:VN</token>
<token>PIA+1+1301W42151600:CG+::184+Y::52+:VN</token>
<token>PIA+5+1117394:AU</token>
<token>
IMD+F++:::Plaster Self Adhesive Blue Assorted 100/Pk
</token>
<token>IMD+C++:20</token>
<token>MEA+ABA+:::EACH</token>
<token>QTY+21:3:EA</token>
<token>QTY+169:3:EA</token>
<token>QTY+182:0:EA</token>
<token>QTY+194:0:EA</token>
<token>QTY+256:0:EA</token>
<token>DTM+2:201606170000:203</token>
<token>MOA+128:30.54</token>
<token>MOA+146:10.18</token>
<token>FTZ+LIN</token>
<token>RFF+ON:1911677</token>
<token>RFF+FI:1/1</token>
<token>RFF+ABO:1911677/1/1</token>
<token>RFF+ADE:PKE000</token>
<token>
NAD+ST++ZZ ZZ ZZ ZZ:ZZ ZZ, ZZ ZZ,:ZZZ+++ZZZZ+:162::United Kingdom+ZZZ ZZZ
</token>
<token>LOC+8+:::A3101A</token>
<token>CTA+PD+:Requestor on Header</token>
<token>UNS+S</token>
<token>UNT+46+02585706</token>
<token/>
</Segment>

from that variable i called another xquery

    <Segment>{ for $id in $body//token/text()
    let $x := fn:substring-before($id,"+")
    return
    element {$x} {$id} }
    </Segment>

final output

    <Segment>
<UNB>
UNB+UNOA:1+5013546126405::CCC+5016237006116+160729:1253+00952177
</UNB>
<UNH>UNH+02585706+ORDCHG:D:99B:UN</UNH>
<BGM>
BGM+230:::Purchase Order Change V201+1911677::0+5+AB
</BGM>
<DTM>DTM+137:201607291253:203</DTM>
<DTM>DTM+4:201606150000:203</DTM>
<DTM>DTM+154:201608080000:203</DTM>
<RFF>RFF+CT:XXXX/0140</RFF>
<NAD>
NAD+VN++XXX HOUSE:XXXX CLOSE::XXX+XX XX XX XX LIMITED++XXX+:162::United Kingdom+XX 0XX
</NAD>
<LOC>LOC+80+:::U3F36</LOC>
<CTA>CTA+CW</CTA>
<COM>COM+N/A:TE</COM>
<NAD>NAD+ST++9999++++:162::United Kingdom</NAD>
<NAD>
NAD+BT++XX XX IP:XX HOUSE,:XX XX,:XX+++XX,+:162::United Kingdom+XX XXX
</NAD>
<LOC>LOC+2+:::XXXXX</LOC>
<NAD>
NAD+BY++XX XX XX:XX HOUSE:XX XX:XX+++XX+:162::UnitedKingdom+XXX XXXX
</NAD>
<LOC>LOC+158+:::XXXXXX</LOC>
<RFF>RFF+VA</RFF>
<CTA>CTA+OC+:XX XX</CTA>
<COM>COM+01593424302:TE</COM>
<CTA>CTA+PD+:POWELL, WO(1,2) XXX</CTA>
<COM>COM+01600 711338:TE</COM>
<CUX>CUX+2:GBP</CUX>
<PAT>PAT+ZZZ+7:::Immediate</PAT>
<LIN>LIN+1++:VN</LIN>
<PIA>PIA+1+1301W42151600:CG+::184+Y::52+:VN</PIA>
<PIA>PIA+5+1117394:AU</PIA>
<IMD>
IMD+F++:::Plaster Self Adhesive Blue Assorted 100/Pk
</IMD>
<IMD>IMD+C++:20</IMD>
<MEA>MEA+ABA+:::EACH</MEA>
<QTY>QTY+21:3:EA</QTY>
<QTY>QTY+169:3:EA</QTY>
<QTY>QTY+182:0:EA</QTY>
<QTY>QTY+194:0:EA</QTY>
<QTY>QTY+256:0:EA</QTY>
<DTM>DTM+2:201606170000:203</DTM>
<MOA>MOA+128:30.54</MOA>
<MOA>MOA+146:10.18</MOA>
<FTX>FTX+LIN</FTX>
<RFF>RFF+ON:1911677</RFF>
<RFF>RFF+FI:1/1</RFF>
<RFF>RFF+ABO:1911677/1/1</RFF>
<RFF>RFF+ADE:PKE00</RFF>
<NAD>
NAD+ST++XX XX XX XX:XX XX, XX XX,:XXX+++XXXX+:162::United Kingdom+XXX XXX
</NAD>
<LOC>LOC+8+:::A3101A</LOC>
<CTA>CTA+PD+:Requestor on Header</CTA>
<UNS>UNS+S</UNS>
<UNT>UNT+46+02585706</UNT>
<UNH>UNH+02585706+ORDCHG:D:99B:UN</UNH>
<BGM>
BGM+230:::Purchase Order ChangeV201+1911677::0+5+AB
</BGM>
<DTM>DTM+137:201607291253:203</DTM>
<DTM>DTM+4:201606150000:203</DTM>
<DTM>DTM+154:201608080000:203</DTM>
<RFF>RFF+CT:ZZZZ/0140</RFF>
<NAD>
NAD+VN++ZZZ HOUSE:ZZZZ CLOSE::ZZZ+ZZ ZZ ZZ ZZ LIMITED++ZZZ+:162::United States+ZZ 0ZZ
</NAD>
<LOC>LOC+80+:::U3F36</LOC>
<CTA>CTA+CW</CTA>
<COM>COM+N/A:TE</COM>
<NAD>NAD+ST++9999++++:162::United Kingdom</NAD>
<NAD>
NAD+BT++ZZ ZZ IP:ZZ HOUSE,:ZZ ZZ,:ZZ+++ZZ,+:162::United Kingdom+ZZ ZZZ
</NAD>
<LOC>LOC+2+:::ZZZZZ</LOC>
<NAD>
NAD+BY++ZZ ZZ ZZ:ZZ HOUSE:ZZ ZZ:ZZ+++ZZ+:162::UnitedKingdom+ZZZ ZZZZ
</NAD>
<LOC>LOC+158+:::ZZZZZZ</LOC>
<RFF>RFF+VA</RFF>
<CTA>CTA+OC+:ZZ ZZ</CTA>
<COM>COM+01593424302:TE</COM>
<CTA>CTA+PD+:POWELL, WO(1,2) ZZZ</CTA>
<COM>COM+01600 711338:TE</COM>
<CUZ>CUZ+2:GBP</CUZ>
<PAT>PAT+ZZZ+7:::Immediate</PAT>
<LIN>LIN+1++:VN</LIN>
<PIA>PIA+1+1301W42151600:CG+::184+Y::52+:VN</PIA>
<PIA>PIA+5+1117394:AU</PIA>
<IMD>
IMD+F++:::Plaster Self Adhesive Blue Assorted 100/Pk
</IMD>
<IMD>IMD+C++:20</IMD>
<MEA>MEA+ABA+:::EACH</MEA>
<QTY>QTY+21:3:EA</QTY>
<QTY>QTY+169:3:EA</QTY>
<QTY>QTY+182:0:EA</QTY>
<QTY>QTY+194:0:EA</QTY>
<QTY>QTY+256:0:EA</QTY>
<DTM>DTM+2:201606170000:203</DTM>
<MOA>MOA+128:30.54</MOA>
<MOA>MOA+146:10.18</MOA>
<FTZ>FTZ+LIN</FTZ>
<RFF>RFF+ON:1911677</RFF>
<RFF>RFF+FI:1/1</RFF>
<RFF>RFF+ABO:1911677/1/1</RFF>
<RFF>RFF+ADE:PKE000</RFF>
<NAD>
NAD+ST++ZZ ZZ ZZ ZZ:ZZ ZZ, ZZ ZZ,:ZZZ+++ZZZZ+:162::United Kingdom+ZZZ ZZZ
</NAD>
<LOC>LOC+8+:::A3101A</LOC>
<CTA>CTA+PD+:Requestor on Header</CTA>
<UNS>UNS+S</UNS>
<UNT>UNT+46+02585706</UNT>
</Segment>

The above 2 step is working only for some edi files, I am getting the same error sometime for few other edi files. Could anyone review my steps and advise me. If possible in one xquery.

Thanks Sree

dirkk dirkk · Accepted Answer · 2017-06-28T10:34:09

First of all, a QName can not contain whitespaces, so you should remove the whitespaces at the beginning using fn:normalize-space().

Secondly, because you get the substring of your string with everything before UNZ, the last character of your string will be the '. Hence, when you later tokenize this string based on ', the last element of the sequence will be an empty string. You can avoid this by either cutting out the last ' as well or you might want to check for non-empty elements. The first solution would look like this (see the 'UNZ):

<Segment>{ for $id in fn:tokenize(fn:substring-before(fn:substring-after($body,""),"'UNZ"),"'")
     let $x := fn:normalize-space(fn:substring-before(fn:substring-after($id,""),"+"))
     return
     element {$x} {$id} }
    </Segment>

and the second one like this:

<Segment>{ for $id in fn:tokenize(fn:substring-before(fn:substring-after($body,""),"UNZ"),"'")[. != ""]
     let $x := fn:normalize-space(fn:substring-before(fn:substring-after($id,""),"+"))
     return
     element {$x} {$id} }
    </Segment>

XML element dynamic in xquery

1 Answers