4 Replies Latest reply: Aug 13, 2013 7:58 AM by remcobrand RSS

    how to get multiple xml-elements in a 1:n relation without using xmlaggregation

    remcobrand

      I need to create the following xml-structure from an oracle-database

      where each survey can multiple deelnemers (participants)

      I'm not sure to create this using XMLElement without getting the message "ORA-01427 subsquery returns more then one row"

      I could try to use dbms_xmldom (never done that before) but I'm wondering if anyone knows how to generate this just using the plain Oracle SQL-XMLfunctions (XMLElement, XMLAggr)

          <NieuweSurveys>

              <Survey>

                  <Surveynaam>2013-02-01</Surveynaam>          

                  <Startdatum>2013-02-01</Startdatum>

                  <Einddatum>2013-02-15</Einddatum>

                  <Deelnemer>

                      <Voornaam>Tilde</Voornaam>

                      <Tussenvoegsel/>

                      <Achternaam>DeelnemerA</Achternaam>

                      <Geslacht>man</Geslacht>

                      <Emailadres>tilde.deelnemer.a@tjip.com</Emailadres>

                      <Voorkeurstaal>nl</Voorkeurstaal>

                      <Account>Schouten & Nelisen</Account>

                      <Functie>bouwer</Functie>

                  </Deelnemer>

                  <Deelnemer>

                      <Voornaam>Tilde</Voornaam>

                      <Tussenvoegsel/>

                      <Achternaam>DeelnemerB</Achternaam>

                      <Geslacht>vrouw</Geslacht>

                      <Emailadres>tilde.deelnemer.b@tjip.com</Emailadres>

                      <Voorkeurstaal>nl</Voorkeurstaal>

                      <Account>Schouten & Nelisen</Account>

                      <Functie>tester</Functie>

                  </Deelnemer>

              </Survey>

      </NieuweSurveys>

        • 1. Re: how to get multiple xml-elements in a 1:n relation without using xmlaggregation
          odie_63

          I could try to use dbms_xmldom (never done that before) but I'm wondering if anyone knows how to generate this just using the plain Oracle SQL-XMLfunctions

          We can build any possible structure using SQL/XML functions.

          Now the correct way to do it depends on the structure of your base tables.

           

          Could you give some sample data and show what you've tried so far ?

           

          Thanks.

          • 2. Re: how to get multiple xml-elements in a 1:n relation without using xmlaggregation
            remcobrand

            From offerings (classes) , each offering having multiple registrations,

            an xml-file is to be generated which is used to sent the participants a survey-form

             

            The following query leads to

             

            select XMLElement

                   ( "NieuweSurveys"

                   , XMLAgg

                    (XMLElement

                   ( "Survey"

                       , ( select XMLAgg

                                    ( XMLElement

                                      ( "deelnemer"

                                      , XMLElement("emailadres",emplo.email)

                                      )

                                    )

                             from tpt_registration  regdw

                             inner join cmt_person emplo

                                     on emplo.id = regdw.student_id

                             where regdw.class_id = offer.id

                           ) as "deelnemers"

                    )

                  )

                  )

                  )            

            from tpt_dummy_offering offer

             

            gives

             

                <NieuweSurveys>

                    <Survey>

                    <Deelnemers>

                        <Deelnemer>

                            <Voornaam>Tilde</Voornaam>

                            <Tussenvoegsel/>

                            <Achternaam>DeelnemerA</Achternaam>

                            <Geslacht>man</Geslacht>

                            <Emailadres>tilde.deelnemer.a@tjip.com</Emailadres>

                            <Voorkeurstaal>nl</Voorkeurstaal>

                            <Account>Schouten & Nelisen</Account>

                            <Functie>bouwer</Functie>

                        </Deelnemer>

                        <Deelnemer>

                            <Voornaam>Tilde</Voornaam>

                            <Tussenvoegsel/>

                            <Achternaam>DeelnemerB</Achternaam>

                            <Geslacht>vrouw</Geslacht>

                            <Emailadres>tilde.deelnemer.b@tjip.com</Emailadres>

                            <Voorkeurstaal>nl</Voorkeurstaal>

                            <Account>Schouten & Nelisen</Account>

                            <Functie>tester</Functie>

                        </Deelnemer>

                      <Deelnemers>

                    </Survey>

            </NieuweSurveys>

             

            The <Deelnemers> tag shouldn't be there. The receiving organisation indicated they don't want it in this format.

             

            whiles

             

            select XMLElement

                   ( "NieuweSurveys"

                   , XMLAgg

                    (XMLElement

                   ( "NieuweSurvey"

                       , ( select XMLElement

                                      ( "deelnemers"

                                      , XMLElement("emailadres",emplo.email)

                                      )

                             from tpt_registration  regdw

                             inner join cmt_person emplo

                                     on emplo.id = regdw.student_id

                             where regdw.class_id = offer.id

                           )

                    )

                  )

                  )             

            from tpt_dummy_offering offer

             

            returns "ORA-01427 subquery returns more then one row" 

            (which is true ofcourse)

            • 3. Re: how to get multiple xml-elements in a 1:n relation without using xmlaggregation
              odie_63

              What's your database version ? (SELECT * FROM v$version)

               

              The query doesn't match the output you claim it produces.

              Anyway, the problem is this :

              ) as "deelnemers"

              Putting an alias here shouldn't generate an element - unless you're using XMLForest in your real query without your telling us.

              That's why I ask about the db version, it could be an old bug that's been fixed now (as I don't reproduce the behaviour on 11.2).

               

              What happens when you remove the alias ?

              • 4. Re: how to get multiple xml-elements in a 1:n relation without using xmlaggregation
                remcobrand

                Yes, removing the alias did the trick. Thanx

                The db-version is 10.2.0.40

                I've tried it on a local 11 db and there it even worked without removing the alias