SQL and Xml

  • Hi All,

    I'm just getting to grips with the XML features of SQL Server. I'm going to use an .XSD schema to deliver data from the DB in XML format. One general point though. If any source columns contain quotation marks (") or angled brackets (<>) then presumably this will break my XML if such characters aren't removed. I was thinking of 'fronting' all my tables with views which would filter out all offending characters, but I'd end up doing at least 3 REPLACE() statements server side per column which seems very expensive. From an application perspective it's actually OK to 'dump' these characters. Is there a native/nice solution for this - I can't believe I'm the first to run into it...

    Thanks in advance

    Paul Armstrong

  • SQL actually changes them for you. eg: < becomes "$lt;" etc.

    No matter what, it creates well-formed xml.

    Crispin

    Why don't you try practicing random acts of intelligence and senseless acts of self-control?

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • Hi Crispin,

    Thanks for the response. That seems to have put the issue to bed fairly quickly!

    Paul

  • Paul watch out for putting spaces in your alias'es for your table and or column names. SQL Xml will convert them to the encoded version. AFAIK (I am not at a machine with SQL at the moment) it will turn a space into _x0032_ which is the haxadecimal version of the ascii character for space with some extra formatting. Take a look at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/xmlsdk/htm/xsl_whitespace_4o1f.asp

    Also it is hard to type this with my 3yr old boy jumping up and down begind me in my chair. He seems to think I am Mt. Fuji.

    Tim C.

    //Will write code for food


    Tim C //Will code for food

  • Thanks for the additional tip Tim - it's nice to know these 'gotachas' BEFORE I start discovering them.

    I know what you mean about the Mount Fuji thing. I have 3 boys: a 5 year old, 4 year old and 6 months!

    Paul

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply