9 Replies Latest reply on Sep 5, 2012 9:08 AM by kaostika

    SQL to XML

    kaostika

      Here is my SQL command

       

       

      SELECT id,firstname,lastname

      FROM contacts

       

      It returns

       

      1,Oreste,Schiavone

      2,John,Smith

      3,Mark,Hunt

       

      I want

       

      <id>1</id><firstname>Oreste</firstname><Lastname>Schiavone</lastname> etc

       

      I read this is possible in SQL, But I have not seen it work in FM

       

      Oreste

        • 1. Re: SQL to XML
          comment

          What do you need this for? You can get the specified result directly by exporting as XML - without going through SQL first.

          • 2. Re: SQL to XML
            kaostika

            I want to use the SQL in a Dataviewer.  XML structure allows me to use the result the way I want.

            • 3. Re: SQL to XML
              jrenfrew

              ExecuteSQL( "SELECT  '<id>' || id || '</id><firstname' || firstname || '</firstname><Lastname> || Lastname || '</Lastname>' FROM contacts" ; "" ; "" )

               

              Not sure how far you can go with this.

              • 4. Re: SQL to XML
                comment

                kaostika wrote:

                 

                I want to use the SQL in a Dataviewer.  XML structure allows me to use the result the way I want.

                 

                I am not sure I follow. XML structure in a Data Viewer? How would this be useful?

                • 5. Re: SQL to XML
                  kaostika

                  When I get it to work I will show u.  ;). 

                   

                  Sent from my iPhone

                  • 6. Re: SQL to XML
                    comment

                    Ok, so I'll wait until you show me and then I will tell you how to get it to work...

                    • 7. Re: SQL to XML
                      steve_ssh

                      In some experimentation that I was doing earlier this year, I used the approach that jrenfrew suggests, and it worked fine for me.

                       

                      One caveat:

                       

                      Using the code in the form does not protect you from any parse errors that may occur should your data ever contain a  '<'  char.

                       

                      Because of this I also experimented with variations on the above which were wrapped in either an Evaluate or a Substitute function.  If I recall correctly, because the data returned by ExecuteSQL was being passed as an argument to a function, there was an upper limit to how many rows I could have returned before this would break, but it was a very generous upper limit, and it probably would have served my purposes just fine,

                       

                       

                       

                      Why I was doing this sort of thing:

                       

                      For me, the motivation behind doing this was to generate HTML to go straight into a webviewer on FM Go.  This allowed me to use the webviewer to generate scrolling lists that rendered and scrolled much more smoothly than portals did in the first version of FM Go 12.  Using event handlers to detect clicks, and using the FMP URL protocol, I was able to create scrolling lists for choosing items by clicking them.  Really, it was nothing different than what could be accomplished with a portal, other than it looked better when it scrolled because you could watch all the data fly by instead of seeing the scroll content turn grey during fast scrolling of a long list.  The list was nothing but a bunch of DIV elements, formatted with some CSS.  The CSS was defined to have the width of the DIVs fit exactly into the width of the webviewer without any lateral scrolling.  The actual length of the list of DIVs extended well beyond the height of the webviewer object, but the scrollability of the webviewer content on GO essentially turned the content into a scrolling list that looked very neat and natural.

                       

                      Just for grins, I started adding swipe handlers to detect a horizontal swipe across one of the "portal" rows.  I thought this might be a little bit of fun iOS-like "spice" to be able to add to the UI, going for the effect that one sees when deleting a file from the file list on Go by swiping.  It seemed totally doable -- the event handling mechanism framework on iOS made it pretty simple to detect a swipe.  A this point I coined the term "rich value list" for this experimental project.  At the time that I was playing with this I thought it was all pretty cool stuff.  That said, with each portal rendering performance improvement that FM rolls out on GO, it really becomes less a useful workaround, and more just a fun experiment.  I plan to pick the project up again sometime, just for grins.

                      • 8. Re: SQL to XML
                        kaostika

                        I am doing the same.   But on the clients. Not just Go.

                         

                        Sent from my iPhone

                        • 9. Re: SQL to XML
                          kaostika

                          Lol

                           

                          Sent from my iPhone