1 Reply Latest reply: Jul 18, 2013 1:53 PM by odie_63 RSS

    Getting XML from Five Different Oracle Tables

    user5768410

      I need to get xml like

       

      <dataset code="123" title="" pubcode="456" minrows="0">

          <schema code="s1" /> <!-- can be one -->

          <rowset code="rs1" /> <!-- can be one -->

          <sorter>

              <!-- field can be MORE than one -->

              <field name="field1" order="o1"/>

              <field name="field2" order="o2"/>

          </sorter>

          <!-- filter can be MORE than one -->

          <filter type="filter1" value="val1" />

          <filter type="filter2" value="val2" />

      </dataset>

       

      Where each tag corresponds to a a separate table. And each attribute in that tag is a column in the corresponding table Have written below sql for same

       

      SELECT  XMLELEMENT(NAME "dataset",

                             XMLAttributes(ds.DataSet_Code AS "code",ds.DataSet_Title as "title",ds.pub_code as "pubcode",ds.Min_Rows as "minrowss"),

                             XMLFOREST( 

                                  SELECT XMLElement("schema", XMLAttributes(fs.schema_code AS "code"))

                                  FROM File_Schema fs WHERE fr.dataset_code = ds.dataset_code,

                                  SELECT XMLElement("rowset", XMLAttributes(fr.rowset_code AS "code")) FROM File_RowSet fr

                                    WHERE fr.dataset_code = ds.dataset_code,

                                  SELECT XMLELEMENT(NAME "sorter",

                                              XMLAGG(XMLELEMENT(NAME "field",

                                                                  XMLATTRIBUTES(fsf.field_name AS "name",fsf.field_order AS "order")

                                                                )

                                                      )

                                              )

                                  FROM File_sorter_field fsf WHERE fsf.dataset_code=ds.dataset_code,

                                  SELECT XMLAGG(XMLELEMENT(NAME "filter", XMLATTRIBUTES(type AS "type",value AS "value")))

                                              FROM File_Filter ff where ff.dataset_code=ds.dataset_code

       

                          ))

      FROM File_Product fp , File_DataSet ds

      WHERE fp.File_Name = ds.File_Name and fp.File_Name = 'abc' and ds.dataset_code ='123' ;

       

      for which I get error like below

        ORA-00936: missing expression 00936. 00000 - "missing expression" *Cause: *Action: Error at Line: 4 Column: 28

       

      any help appreciated

        • 1. Re: Getting XML from Five Different Oracle Tables
          odie_63

          Scalar subqueries have to be wrapped in parentheses.

           

          Also, you probably don't want to use XMLForest in this case. XMLForest requires an alias for each argument, and generates an additional element with that name.

           

          Maybe something like this is more appropriate :

          SELECT XMLELEMENT("dataset"

                 , XMLAttributes(

                     ds.DataSet_Code AS "code"

                   , ds.DataSet_Title as "title"

                   , ds.pub_code as "pubcode"

                   , ds.Min_Rows as "minrows"

                   )

                 , (

                     SELECT XMLElement("schema", XMLAttributes(fs.schema_code AS "code"))

                     FROM File_Schema fs

                     WHERE fr.dataset_code = ds.dataset_code

                   )

                 , (

                     SELECT XMLElement("rowset", XMLAttributes(fr.rowset_code AS "code"))

                     FROM File_RowSet fr

                     WHERE fr.dataset_code = ds.dataset_code

                   )

                 , (

                     SELECT XMLELEMENT("sorter"

                            , XMLAGG(

                                XMLELEMENT("field"

                                , XMLATTRIBUTES(

                                    fsf.field_name AS "name"

                                  , fsf.field_order AS "order"

                                  )

                                )

                              )

                            )

                     FROM File_sorter_field fsf

                     WHERE fsf.dataset_code = ds.dataset_code

                   )

                 , (

                     SELECT XMLAGG(

                              XMLELEMENT("filter"

                              , XMLATTRIBUTES(

                                  type AS "type"

                                , value AS "value"

                                )

                              )

                            )

                      FROM File_Filter ff

                      WHERE ff.dataset_code = ds.dataset_code

                   )         

                 )

          FROM File_Product fp

               JOIN File_DataSet ds ON fp.File_Name = ds.File_Name

          WHERE fp.File_Name = 'abc'

          AND ds.dataset_code ='123' ;