Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2012
    Posts
    2

    Question Unanswered: Need final touch in XMLQUERY

    Hi all,

    Here is my xml structure

    <row id="YY1200300003">
    <c1>30872.01</c1>
    <c2>2623</c2>
    <c3>TEXT.A</c3>
    <c3 m="1" s="2">TEXT.AA</c3>
    <c3 m="2">TEXT.B</c3>
    <c3 m="4">TEXT.D</c3>
    </row>

    In my final output I would like to have: id, value_c3:m;s#
    in my case, like this
    YY1200300003,TEXT.A#TEXT.AA:1;2#TEXT.B:2#TEXT.D:4#

    -------------
    What I have till now is: id, value_c3#
    in my case is like YY1200300003,TEXT.A#TEXT.AA#TEXT.B#TEXT.D#

    My current select is below, what I don't know how to "insert" values "m" and "s" in my final output

    SELECT RECID, xmlserialize(XMLQUERY('for $d in $t/row/c3 return concat($d/text(),"#")' passing XMLRECORD as "t") as varchar(4000) EXCLUDING XMLDECLARATION) FROM BALANCES WHERE RECID = 'YY1200300003'

    Any help will be more than welcome.

  2. #2
    Join Date
    Jun 2012
    Posts
    2

    Thumbs up Problem solved

    This is my sql, which I want

    SELECT RECID, xmlserialize(XMLQUERY('for $d in $t/row/c3 return ( $d/text(),":",$d/data(@m) ,";",$d/data(@s),"#")' passing XMLRECORD as "t") as varchar(4000) EXCLUDING XMLDECLARATION) FROM BALANCES WHERE RECID = 'YY1200300003'

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •