6 Replies Latest reply on Jun 4, 2012 5:36 AM by rajkota

    Column value as identifier in XMLELEMENTmethod

    rajkota

      Hi,

       

      We are using Teiid 7.7. I have a concern on SQL / XML.

       

      I have a SQL as Select A.c1,A.c2,A.c3 from tab as A and the value of c2 will be the same for all the rows.

       

      I want to convert the values of the columns to XML. Also I want to retrieve the identifier value from the column's value

       

      Can I write the SQL as follows where the identifier name is the c1's column value.

       

      Select A.c2, xmlelement(A.c1, XMLATTRIBUTES(convert(A.C3, String) as "SYSID")) from tab as A

       

      We tried using this but there was a syntax error.

       

      Is there any way to achive this using any other method?

       

      Thanks

      Raj

        • 1. Re: Column value as identifier in XMLELEMENTmethod
          shawkins

          Since it's in the spec most SQL/XML implementations require the element name to be an identifier.  To be more dynamic, you could use explicit element construction via XMLQUERY:

           

          SELECT A.c2, XMLQUERY('element {$n} {attribute SYSID {$attr}}' passing A.c1 AS n, convert(A.c3, string) AS attr) FROM tab AS A

           

          Steve

          • 2. Re: Column value as identifier in XMLELEMENTmethod
            rajkota

            Thanks Steve. Your reply has helped me to continue my research in my work.

             

            Raj

            • 3. Re: Column value as identifier in XMLELEMENTmethod
              rajkota

              Hi Steve,

               

              If I run the below query:

               

              SELECT xmlserialize(XMLQUERY('element {$n} {attribute SYSID {$attr}, attribute NID{$attr1}}' passing A.c1 AS n, convert(A.c3, string) AS attr,convert(A.c4, string) AS attr1)) FROM tab AS A

               

              I get the response as:

               

              expr

              ---------------------------------------------------------

              <ELEMENT1 SYSID="SYSID1 " NID="NID1"/>

               

              but I am expecting response like <ELEMENT1 SYSID="SYSID1 " NID="NID1"></ELEMENT1>

               

              Could you please let me know how can I achieve it?

               

              Thanks,

              Raj

              • 4. Re: Column value as identifier in XMLELEMENTmethod
                rajkota

                As an alternative, String scalar function 'replace()' is helping me to get the desired result.

                 

                Now if I run the same query with replace () method:

                 

                SELECT replace(xmlserialize(XMLQUERY('element {$n} {attribute SYSID {$attr}, attribute NID{$attr1}}' passing A.c1 AS n, convert(A.c3, string) AS attr,convert(A.c4, string) AS attr) as string), '/>', concat(concat('></', A.c1), '>') ) FROM tab AS A

                 

                I get the response like:

                 

                expr

                ---------------------------------------------------------

                <ELEMENT1 SYSID="SYSID1 " NID="NID1"></ELEMENT>

                 

                Thanks,

                Raj

                • 5. Re: Column value as identifier in XMLELEMENTmethod
                  shawkins

                  Raj,

                   

                  The forms are equivalent and I'm not aware of any output property that would put the full element form rather than the short-hand.  The replace workaround should be fine or if you can add whitespace to the element content, then the end tag will automatically be added:

                   

                  XMLQUERY('element {$n} {attribute SYSID {$attr}, " "}' passing ...

                   

                  Steve

                  • 6. Re: Column value as identifier in XMLELEMENTmethod
                    rajkota

                    Hi Steve,


                    Thank you. I tried this way, But I have to compare the result with another xml string, which does not have space between starting and ending tags. So I have no option other than replace () for my requirement and which is working quite decently.

                     

                    Thanks,

                    Raj