6 Replies Latest reply: May 9, 2012 1:40 AM by 829385 RSS

    XMLAttribute unable to handle multiple values returning from a query

    829385
      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
          Marco Gralike
          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
            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
              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
                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
                  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
                    That did the work.. Thanks a lot man...i was missing the function XMLAgg...

                    Thanks a lot once again

                    Cheers