SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Wizard aaron.martone's Avatar
    Join Date
    Feb 2003
    Location
    Florida, USA
    Posts
    2,322
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb Storing and conerting data from DB to XML-safe

    I am using SQL 2005 for a DB and ColdFusion for an Application language. Is there a way in SQL that it can convert all fields I specify as XML-safe text?

    For example, if the database stored: "Hello, my name is Aaron, what's yours?"

    I could always use ColdFusion's XMLFormat() function (I'd just have to use it everywhere I reference data pulled from the DB, which is kind of taxing) to ensure that it was converted to:

    “Hello, my name is Aaron, what's yours?”

    So, is there a way SQL 2005 can do this conversion for me before I pass the data off to the application language?

  2. #2
    SitePoint Member
    Join Date
    Nov 2011
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Probably the quickest method would be to write your own XML entity encoder using the Replace method:

    Replace(Replace(Replace(Replace(Replace(columnName,'&','&amp;'),'<', '&lt;'),'>', '&gt;'),'"', '&quot;'), '''', '&#39;')

    Just replace columnName with your column and use the above in your Select statements. I haven't tested the above but it should be pretty close to what you need.

  3. #3
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,627
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    With SQL 2005, you can actually SELECT XML straight from the DB. Check out FOR XML AUTO to start with.

    On the application layer, you probably want to look into serialization over hand encoding. Xml is not a string even if it can be expressed as one.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •