This discussion is archived
6 Replies Latest reply: May 8, 2012 11:40 PM by 829385 RSS

XMLAttribute unable to handle multiple values returning from a query

829385 Newbie
Currently Being Moderated
Hi Guys,

I have to design the format of the data returning from a query in XML. For this i have used xmlelement and xmlattribute

the query goes like this



SELECT '<?xml version="1.0" ?>'||' '||XMLELEMENT("imageData",
XMLATTRIBUTES(LI.GROUPNO as "imageName", 'PNG'as "imageType", '2.0' as "version", 'com.snapon.sbs' as "xmlns"),
XMLELEMENT("callouts",
XMLATTRIBUTES('roundedRectangle' as "shape"),
XMLELEMENT("callout",
XMLATTRIBUTES((SELECT IO.PNC_KEYNO FROM PFEALIOCR IO WHERE IO.BASECATALOGNO = LI.BASECATALOGNO AND LI.GROUPNO = IO.GROUPNO) as "label"),
XMLELEMENT("point",
XMLATTRIBUTES((SELECT IO.COORDINATESX1 FROM PFEALIOCR IO WHERE IO.BASECATALOGNO = LI.BASECATALOGNO AND LI.GROUPNO = IO.GROUPNO) as "x",(SELECT IO.COORDINATESY1 FROM PFEALIOCR IO WHERE IO.BASECATALOGNO = LI.BASECATALOGNO AND LI.GROUPNO = IO.GROUPNO) as "y")))))
FROM PFEALIGIL LI
WHERE LI.DELETEFLAG <> 'D'

here he logic is the one groupno from pfealigil and the corresponding pnc_keyno that are related to groupno are fetched here. but here since multiple pnc_keyno are returning xmlattributes is unable to handle. I want to design the xml in this format

<?xml version="1.0" ?>
<imageData imageName="ISDH0001104"
imageType="PNG" version="2.0" xmlns="com.snapon.sbs">
<callouts shape="roundedRectangle">
<callout label="182">
<point x="289" y="68"/>
</callout>
<callout label="247"<point x="430" y="83"/>
</callout>
<callout label="122"><point x="546" y="331"/>
</callout>
<callout label="249"><point x="402" y="429"/>
</callout>
<callout label="248"><point x="392" y="463"/>
</callout>
<callout label="182"><point x="228" y="416"/>
</callout>
<callout label="1"><point x="364" y="737"/>
</callout>
<callout label="14"><point x="494" y="980"/>
</callout>
<callout label="168"><point x="671" y="910"/>
</callout>
<callout label="15"><point x="779" y="848"/>
</callout>
<callout label="1"><point x="805" y="343"/>
</callout>
</callouts>
</imageData>


like here one image name all those pnc_keyno that are valid should come in this way as above. Please let me know if anything is unclear to you
  • 1. Re: XMLAttribute unable to handle multiple values returning from a query
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    database version is...? (all digits please)

    For example, if on 11 then there would be no need to concatenate the prolog but you could use XMLROOT
  • 2. Re: XMLAttribute unable to handle multiple values returning from a query
    odie_63 Guru
    Currently Being Moderated
    It's difficult to help without knowing your data model.
    So, in addition, please post some sample data from your base tables and explain the relationships between them.
  • 3. Re: XMLAttribute unable to handle multiple values returning from a query
    829385 Newbie
    Currently Being Moderated
    Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
    PL/SQL Release 10.2.0.5.0 - Production
    CORE     10.2.0.5.0     Production
    TNS for Solaris: Version 10.2.0.5.0 - Production
    NLSRTL Version 10.2.0.5.0 - Production
  • 4. Re: XMLAttribute unable to handle multiple values returning from a query
    829385 Newbie
    Currently Being Moderated
    Data model goes like this
    91-921     1125AE     000000127     000000730
    91-921     18642F     000000513     000000891
    91-921     18643D     000000620     000000844
    91-921     18649E     000000561     000000688
    91-921     18668B     000000620     000000864
    91-921     92101A     000000587     000000591
    91-921     92102A     000000587     000000571
    91-924     1243BD     000000617     000000889

    the 91-921 series is the groupno

    1125AE and so on are the pnc_keyno and other are COORDINATESX1 and COORDINATESY1

    i want the xml should like


    <?xml version="1.0" ?>
    <imageData imageName="91-921"
    imageType="PNG" version="2.0" xmlns="com.snapon.sbs">
    <callouts shape="roundedRectangle">
    <callout label="182">
    <point x="289" y="68"/>
    </callout>
    <callout label="1125AE"<point x="127" y="730"/>
    </callout>
    <callout label="18642F"><point x="513" y="891"/>
    </callout>
    <callout label="18643D"><point x="620" y="844"/>
    </callout>
    and so on...
    </callouts>
    </imageData>     

    i need a seperate such format for every groupno
  • 5. Re: XMLAttribute unable to handle multiple values returning from a query
    odie_63 Guru
    Currently Being Moderated
    apexStarter wrote:
    Data model goes like this
    You mentioned two tables in your first post, but I guess it's just a matter of joining them to get the above resultset?

    In this case you can do it like this :
    SQL> WITH sample_data AS (
      2    SELECT '91-921' groupno, '1125AE' pnc_keyno, 000000127 coordinatesx1, 000000730 coordinatesy1 FROM dual UNION ALL
      3    SELECT '91-921', '18642F', 000000513, 000000891 FROM dual UNION ALL
      4    SELECT '91-921', '18643D', 000000620, 000000844 FROM dual UNION ALL
      5    SELECT '91-921', '18649E', 000000561, 000000688 FROM dual UNION ALL
      6    SELECT '91-921', '18668B', 000000620, 000000864 FROM dual UNION ALL
      7    SELECT '91-921', '92101A', 000000587, 000000591 FROM dual UNION ALL
      8    SELECT '91-921', '92102A', 000000587, 000000571 FROM dual UNION ALL
      9    SELECT '91-924', '1243BD', 000000617, 000000889 FROM dual
     10  )
     11  SELECT XMLRoot(
     12           XMLElement("imageData",
     13             XMLAttributes(
     14               groupno as "imageName"
     15             , 'PNG'as "imageType"
     16             , '2.0' as "version"
     17             , 'com.snapon.sbs' as "xmlns"
     18             )
     19           , XMLElement("callouts",
     20               XMLAttributes('roundedRectangle' as "shape")
     21             , XMLAgg(
     22                 XMLElement("callout",
     23                   XMLAttributes(pnc_keyno as "label")
     24                 , XMLElement("point",
     25                     XMLAttributes(
     26                       coordinatesx1 as "x"
     27                     , coordinatesy1 as "y"
     28                     )
     29                   )
     30                 )
     31               )
     32             )
     33           )
     34         , version '1.0'
     35         )
     36  FROM sample_data
     37  GROUP BY groupno
     38  ;
    
    XMLROOT(XMLELEMENT("IMAGEDATA",XMLATTRIBUTES(GROUPNOAS"IMAGENAME",'PNG'AS"IMAGETYPE",'2.0'AS"VERSION",'COM.SNAPON.SBS'AS
    ------------------------------------------------------------------------------------------------------------------------
    <?xml version="1.0"?>
    <imageData imageName="91-921" imageType="PNG" version="2.0" xmlns="com.snapon.sbs">
      <callouts shape="roundedRectangle">
        <callout label="1125AE">
          <point x="127" y="730"/>
        </callout>
        <callout label="92102A">
          <point x="587" y="571"/>
        </callout>
        <callout label="92101A">
          <point x="587" y="591"/>
        </callout>
        <callout label="18668B">
          <point x="620" y="864"/>
        </callout>
        <callout label="18649E">
          <point x="561" y="688"/>
        </callout>
        <callout label="18643D">
          <point x="620" y="844"/>
        </callout>
        <callout label="18642F">
          <point x="513" y="891"/>
        </callout>
      </callouts>
    </imageData>
    
    <?xml version="1.0"?>
    <imageData imageName="91-924" imageType="PNG" version="2.0" xmlns="com.snapon.sbs">
      <callouts shape="roundedRectangle">
        <callout label="1243BD">
          <point x="617" y="889"/>
        </callout>
      </callouts>
    </imageData>
  • 6. Re: XMLAttribute unable to handle multiple values returning from a query
    829385 Newbie
    Currently Being Moderated
    That did the work.. Thanks a lot man...i was missing the function XMLAgg...

    Thanks a lot once again

    Cheers

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points