Bit of SQL help please

[_dan_]

Hi chaps,

At work we a pretty large SQL db. Some of the data gets exported as XML. Anyhow, XML doesn't seem to like "quotes" in the title field, so I need to run a script to delete all of the "s in the tables. Specifically in the TITLE field in our MATERIAL table.

Anyone know a good bit of script to help me out.

Cheers, Dan

[pdV6]

How do you arrange the export? Is the database server itself generating the XML or is it a hand-crafted script?

There are several characters that have special meaning in XML and these all need to be replaced:

& --> &
' --> '
> --> >
< --> &lt;
" --> &quot;
£ --> &#163;

[roadsweeper]

As pdV^ says, you khave to replace all these 'special' characters in XML. It should be trivial to write a parsing function that does the replacements for you, we used have one that took two parameters, the XML and a switch indicating which 'direction' to do the conevrsion. There must be loads of similar functions available on t'Interweb.

[JonRB]

If you use PHP as the scripting language then it has functions that automatically escape codes like this.

It also has decent XML support, making things doubly simple.