What is the property name for the text of an XML node RSS

• • Reply
  

  Robert Barne...

  Member

  451 Points

  708 Posts

  What is the property name for the text of an XML node

  Oct 10, 2011 09:03 PM|LINK

  My ASP.Net web site uses a 3rd-party component, the Simile Timeline (http://code.google.com/p/simile-widgets/wiki/Timeline).  The data for the timeline is XML, which I create from my SQL Server database using an XMLReader and SQL query: -
      Select * from dbo.TimeLineHistoryData(@Userid) As Event FOR XML AUTO
  The procedure dbo.TimeLineHistoryData is a table-valued function that returns data with the correct formats and names (for example, "title", not "Title") for the timeline, and with "As Event FOR XML AUTO" generates XML data like this: -
  <Event title="Essex Voyage" start="Sep  3 1842" durationEvent="1" end="Jan 20 1843" caption="Old Family emigrate" color="#336699" textColor="black" />

  Perfect, and everything works OK, until I get to a row containing "description".   The Simile Timeline documentation says: -

  • description - will be displayed inside the bubble with the event's title and image.
    • XML Format: the description is stored as the text content of the event element (see below). Note: the XML standard requires that an element's text content must be escaped/formatted HTML

  The example given is

  <event
         start="Wed May 01 1963 00:00:00 GMT-0600"
         end="Sat Jun 01 1963 00:00:00 GMT-0600"
         isDuration="true"
         title="Oswald moves to New Orleans">
           Oswald moves to New Orleans, and finds employment at the William <!-- The event's description -->
           B. Riley Coffee Company. &lt;i&gt;ref. Treachery in Dallas, p 320&lt;/i&gt;
      </event>
  
  Of course my code treats "description" exactly like "title", generating code like
  <Event title="SS Tainui" start="Aug 31 1923" durationEvent="0" caption="J A Pym" color="#336699" textColor="black"
   description="J A Pym arrives in New Zealand 31 Aug 1923 on this ship" />
  
  The value of description is ignored. However it is used correctly when I manually edit the XML into this form
  <Event title="SS Tainui" start="Aug 31 1923" durationEvent="0" caption="J A Pym" color="#336699" textColor="black">
   J A Pym arrives in New Zealand 31 Aug 1923 on this ship</event>
  
  Is there a property name that is equivalent to the text value in this form? I tried using "Description", "text",
  and "data" but none worked, and I don't see how I can fix this problem without laboriously building the XML by hand.
  
  Alternatively, is there any way to get the SQL query to return the description as the value of the XML node?
  
  Thank you, Robert.

• • Reply
  

  smirnov

  All-Star

  23700 Points

  4056 Posts

  Re: What is the property name for the text of an XML node

  Oct 10, 2011 10:15 PM|LINK

  Try this

  SELECT title as "@title", [image] as "@image",
  	(SELECT [description] FROM testxml)
  FROM testxml FOR XML PATH('event'), ROOT('data')

  The catch is to use a subquery for the description field and specify "as '@attributename'" to get proper formatting for other attributes.

  Test script for

  create table testxml 
  (title nvarchar(100), [image] nvarchar(100), [description] nvarchar(1000))
  go
  
  insert into testxml values ('Timeline',
          'http://...csail-logo.gif',
          'Some text')

  returns

  <data><event title="Timeline" image="http://...csail-logo.gif">Some text</event></data>

  Hope this helps.

  • Edited by smirnov on Oct 10, 2011 10:18 PM
• • Reply
  

  Robert Barne...

  Member

  451 Points

  708 Posts

  Re: What is the property name for the text of an XML node

  Oct 12, 2011 03:55 AM|LINK

  Ingenious.  However I don't think it will work if there is more than one record in TestXML.   I tried these experiments: -

  Step 1.   I wrote:  Select title as "@title", [image] as "@image" from dbo.timelinehistorydata("robertb")

  As I expected this correctly returned the following from my test database.  After all, my virtual table functions like your actual table TestXML: -

  <data>
    <event title="Essex Voyage" />
    <event title="Error" />
    <event title="Battle of Grand Port" image="http://en.wikipedia.org/wiki/File:Grand_Port_mg6971.jpg" />
    <event title="Great Depression" />
    <event title="Treaty of Waitangi" />
    <event title="SS Tainui" />
    <event title="World War 2" />
    <event title="Baltic Action" />
    <event title="Error" />
    <event title="First World War" />
    <event title="Gertrude Voyage" />
    <event title="Franklin's Lost Expedition" />
    <event title="Search Expedition of 1852-3" />
  </data>

  I then added the subquery (SELECT [description] FROM testxml), making the whole query

  Select title as "@title", [image] as "@image", (SELECT [description] FROM dbo.TimeLineHistoryData('robertb')) from dbo.TimeLineHistoryData('robertb') FOR XML PATH('event'), ROOT('data')

  Now the query throws an error message: -

  Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

  In fact, there is actually only one record with the virtual table dbo.TimeLineHistoryData('robertb') with a value for Description.  However in a more general case there could be several.

  If I simply write
        Select title as "@title", [image] as "@image", [description] from dbo.TimeLineHistoryData('robertb') FOR XML PATH('event'), ROOT('data')
  then I get this result for the record that contains a value for [description]: -

    <event title="SS Tainui">
      <description>J A Pym arrives in New Zealand 31 Aug 1923 on this ship</description>
    </event>

  This is not accepted by the Simile timeline, which will not accept the "elements" form of XML.

  Going back to your query but with dbo.TimelineHistoryData(), I added an identity field called "num" to the table returned by the function so that I had something for a unique lookup, and wrote

  Select title as "@title", [image] as "@Image", (Select [description] from dbo.TimeLineHistoryData('robertb') as T2 where t2.num = t1.num)
      from dbo.TimeLineHistoryData('robertb') as T1 FOR XML auto, ROOT('data')

  Now I was blocked by a message: -

  Unnamed tables cannot be used as XML identifiers as well as unnamed columns cannot be used for attribute names. Name unnamed columns/tables using AS in the SELECT statement.

  I wondered about changing the function to return the XML directly, but I'd already failed with this approach.  I'd written my first version of TimelineData to return a string, being the XML that I wanted.  The problem is that it returns the whole table as XML, so it doen't take much to exceed the 2033 character limit.   The only thing that works (that I've found so far) is to use an XML reader handling one row at a time within a loop like this: -

              Dim OutputXML As New StringBuilder
              OutputXML.Append("<data>")
              xmlr = GDBCommand.ExecuteXmlReader()
              xmlr.Read()
              Do While xmlr.ReadState <> System.Xml.ReadState.EndOfFile
                  OutputXML.Append(xmlr.ReadOuterXml())
              Loop
              OutputXML.Append("</data>")

  So at the moment the only solution that I can think of is to change my web handler to read SQL normally (not as XML), and manually create the XML I want within my read loop.  

  Surely there's a better way!

  • Edited by Robert Barnes on Oct 12, 2011 03:56 AM
• • Reply
  

  smirnov

  All-Star

  23700 Points

  4056 Posts

  Re: What is the property name for the text of an XML node

  Oct 12, 2011 09:43 AM|LINK

  Robert Barnes

  I then added the subquery (SELECT [description] FROM testxml), making the whole query

  Select title as "@title", [image] as "@image", (SELECT [description] FROM dbo.TimeLineHistoryData('robertb')) from dbo.TimeLineHistoryData('robertb') FOR XML PATH('event'), ROOT('data')

  Now the query throws an error message: -

  Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

  Try

  Select title as "@title", [image] as "@Image",
      (Select [description] from dbo.TimeLineHistoryData('robertb') as T2 where t2.num = t1.num)
      from dbo.TimeLineHistoryData('robertb') as T1 FOR XML PATH('event'), ROOT('data')

  Hope this helps.

  • Edited by smirnov on Oct 12, 2011 09:44 AM
  • Marked as answer by Robert Barnes on Oct 12, 2011 07:44 PM
• • Reply
  

  Robert Barne...

  Member

  451 Points

  708 Posts

  Re: What is the property name for the text of an XML node

  Oct 12, 2011 08:00 PM|LINK

  Thank you, this works.

  It got a bit complicated in the actual implementation: -
        There are 12 fields in the actual table, so the section 'title as "@title" ...'  becomes quite long
        Because this SQL command must be specified as a String in my VB code, the single " had to be written as ""

  so my actual statement becames

              CommandString = "Select title as ""@title"", [image] as ""@Image"", link as ""@link"", start as ""@start"", latestStart as ""@latestStart"", earliestEnd as ""@earliestEnd"", " & _
                            "[End] as ""@end"", durationEvent as ""@durationEvent"", Caption as ""@caption"", color as ""@color"", textColor as ""@textColor"",(Select [description] from dbo.TimeLineHistoryData('robertb') as T2 where t2.num = t1.num) " & _
                          "from dbo.TimeLineHistoryData(@Userid) as T1 FOR XML PATH('event')"

  I tried to find a simplication.  "Select * from dbo.TimeLineHistoryData(@Userid) as EVENT FOR XML AUTO" is what I was using before, and is ALMOST correct: it works except for [description].  I figured that it wouldn't matter if the XML contained both "description=xxxx" and xxxx as the XML text value so I tried writing

  Select *, (Select [description] from dbo.TimeLineHistoryData('robertb') as T2 where t2.num = Event.num) from dbo.TimeLineHistoryData('robertb') As Event for XML Auto

  However this gave me error message

  Unnamed tables cannot be used as XML identifiers as well as unnamed columns cannot be used for attribute names. Name unnamed columns/tables using AS in the SELECT statement.

  Grrrrrrr!   SQL is very quirky!

  But at least I have a solution that works.  Thank you Smirnov.