8 Replies Latest reply: Jul 9, 2013 7:08 AM by odie_63 RSS

    if field null don't construct a xmlelement or attribute, how? (9i)

    600198
      Hi, can you help me in this question?

      If a field has no value (null) not build the element.

      select
      xlmelement("something",
      xmlelement("name",t1.name),
      xmlelement("job",t1.job) -- t1.job can have no value, therefor shouldnt build the element
      )
      from table1 t1

      the same with atributes.

      is there a way to do this without using decode? (because i have alot of field that can be null).
        • 1. Re: if field null don't construct a xmlelement or attribute, how? (9i)
          AntsHindpere
          Hi,

          Use xmlforest.
          SQL> with t as (select 'John' name, null job from dual)
            2  select
            3    xmlelement("something",
            4      xmlelement("name",name),
            5        xmlforest(job "job")
            6    ) xml
            7  from t;
          
          XML
          --------------------------------------------------------------------------------
          <something><name>John</name></something>
          
          Elapsed: 00:00:00.04
          • 2. Re: if field null don't construct a xmlelement or attribute, how? (9i)
            600198
            Thank you for the reply.

            But why not use xmlforest always, instead of xmlelement?

            <element1>
            <element2>etc</element2>
            <element3>etc</element3>
            <element4>
            <element5>etc</element5>
            </element4>
            </element1>

            what if elements 3 and 4 can be null ... xmlforest can't handle this, can it?


            Plus if a element has atributes but the element itself can
            be null, xmlforest doesnt work in that case -- example:

            <example1>
            <example2>etc</example2>
            <example3 attr1="foo">foobar</example3>
            </example1>

            example 2, and 3 can be null
            this can be other example :
            <example1>
            </example1>

            How do i do this?(i tried xmlforest, can't generate the example )

            (I work with oracle 9i)
            • 3. Re: if field null don't construct a xmlelement or attribute, how? (9i)
              600198
              This is a way to do it, but i dont want to use case, or decode, or if. I would like something like xmlforest.

              select
              xmlelement("PERSON",
                   xmlattributes(name "name"),
                   case when age is null then null else xmlelement("AGE",age) end,
                   case when prof is null then null else xmlelement("PROFF",xmlattributes(years "YEARS"),prof) end
                        
              )
              from
              (select 'Edgar' name,'27' age, null prof,null years from dual)

              result:
              <PERSON name="Edgar">
              <AGE>27</AGE>
              </PERSON>

              as you can see the element prof that has a attribute doesnt show because its null.

              Anyone knows a way to do this, with a function like xmlforest?

              Thanks.
              • 4. Re: if field null don't construct a xmlelement or attribute, how? (9i)
                AntsHindpere
                Hi,

                But why not use xmlforest always, instead of xmlelement?

                Some times you want an empty element.
                XMLforest can't handle attributes as far as i know

                what if elements 3 and 4 can be null ... xmlforest can't handle this, can it?
                SQL> with t as(select 11 element2, null element3,null element5 from dual)
                  2  select xmlelement("element1"
                  3            ,xmlelement("element2",element2)
                  4            ,xmlforest(element3 "element3"
                  5              ,xmlforest(element5 "element5") "element4")) xml from t;
                
                XML
                --------------------------------------------------------------------------------
                <element1><element2>11</element2></element1>
                
                Elapsed: 00:00:00.01
                Ants
                • 5. Re: if field null don't construct a xmlelement or attribute, how? (9i)
                  AntsHindpere
                  Hi,

                  instead of writing
                  case when age is null then null else xmlelement("AGE",age) end
                  you can use shorter version :)
                  case when age is not null then xmlelement("AGE",age) end
                  • 6. Re: if field null don't construct a xmlelement or attribute, how? (9i)
                    600198
                    Thank you,
                    you were very helpful.

                    I must buy you a coffee one of these days, cheers :D .
                    • 7. Re: if field null don't construct a xmlelement or attribute, how? (9i)
                      Ramuplsql10

                      Hi AntsHindeper..

                       

                       

                      SQL> with t as(select 11 element2, null element3,null element5 from dual)
                        2  select xmlelement("element1"
                        3            ,xmlelement("element2",element2)
                        4            ,xmlforest(element3 "element3"
                        5              ,xmlforest(element5 "element5") "element4")) xml from t;

                      In this output

                      <element1><element2>11</element2></element1> 

                       

                      If we want Output like this

                       

                       

                      <element1><element2>11</element2><element3></element3><element5></element5></element1>

                      or

                      <element1><element2>11</element2><element3/>element5/></element5></element1>

                       

                      I know how to do this with xmlelement but i want to it by using the xmlforest and xmlelement.

                       

                      i tried but am not getting.. please help me.

                      • 8. Re: if field null don't construct a xmlelement or attribute, how? (9i)
                        odie_63

                        Funny timing. A similar question has just been brought up again today.

                         

                        And I'll make the exact same answer : if you want to generate tags for empty values, use XMLElement, not XMLForest.