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

    SQL to XML


      Here is my SQL command



      SELECT id,firstname,lastname

      FROM contacts


      It returns






      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



        • 1. Re: SQL to XML

          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

            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

              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

                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

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


                  Sent from my iPhone

                  • 6. Re: SQL to XML

                    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

                      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

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


                        Sent from my iPhone

                        • 9. Re: SQL to XML



                          Sent from my iPhone