0

Is there a way to escape single quotes on the fly, without having to escape every occurrence of a single quote with two single quotes?

I'm looking for a T-SQL equivalent of PL/SQL's quote literal q'[this is PL SQL's quoted literal]'

Just to clarify for future viewers/readers...

Legacy XSL stylesheet which has lots and lots of single and double quotes strewn all over the place is also stored in the database. Every time a change is made to this stylesheet it also needs to be updated in a database column where it is stored.

While I agree it is easy to find and replace all occurrences of a single quote with two single quotes and insert/update the xml value as a string, which is how it was done until now. Two versions had to be done, a script for the stylesheet and the stylesheet itself.

I was looking for a better solution.

@Charlieface's solution fits my requirements

InquisitiveLad
  • 309
  • 3
  • 16
  • Does this answer your question? [How do I escape a single quote in SQL Server?](https://stackoverflow.com/questions/1586560/how-do-i-escape-a-single-quote-in-sql-server) – sanampakuwal Jan 10 '22 at 01:43
  • No, the above doesn't answer my question. I am trying to avoid doubling up single quotes here. Quoted identifier option doesn't work either. I have a big xml file which has lots and lots of single and double quotes within which I am trying to insert/update. – InquisitiveLad Jan 10 '22 at 01:58
  • 1
    Setting Quoted identifier to off and using double quotes to delimit the string would be the only option but has other side effects (including preventing use of xml data type methods) and you can't use it anyway. There are no other options to delimit string literals in TSQL. But easy enough to replace all in a text editor? – Martin Smith Jan 10 '22 at 02:05
  • 1
    You're looking at this the wrong way. You should separate the code and the data, and load it in as a parameter (or perhaps with `OPENROWSET (BULK ...` or `BULK INSERT`). – Charlieface Jan 10 '22 at 02:32
  • Why would you be concerned with quotes in an XML? Are you extracting pieces from XML and constructing dynamic SQL out of them, using them as string literals? – GSerg Jan 10 '22 at 06:57
  • @Charlieface, I am happy with your solution. Happy to accept it as answer. – InquisitiveLad Jan 11 '22 at 10:37
  • I guess someone thought you hadn't done sufficient research and your question was pretty unclear, both of which I agree with although I didn't downvote – Charlieface Jan 11 '22 at 10:39
  • @GSerg, the entire xml is stored in an XML column – InquisitiveLad Jan 11 '22 at 10:39

3 Answers3

1

To escape XML, you can normally just replace ' with '' with a text editor, although I would only do this if doing a small simple upload as a one-off, where I could check there are no other syntax issues. Do not do this programmatically, especially if your data is untrusted.

You should separate the code and data. This means you don't need to escape anything, as the data is not parsed as part of the code.

You can use variables or parameters for this. When calling a procedure from a client app, use a parameter passing in your XML. For example

CREATE OR ALTER PROCEDURE InsertXML
  @xmlData xml
AS

INSERT SomeTable(XmlData)
VALUES (@xmlData);

If you need to do this programmatically and want to load it from a file, you can use OPENROWSET (BULK, for example

INSERT SomeTable(XMLData)
SELECT CONVERT(XML, BulkColumn)
FROM OPENROWSET(BULK 'C:\YourXML.xml', SINGLE_BLOB) AS x;

The file needs to be on the server, not the client.

You can also load files using various tools.

Charlieface
  • 52,284
  • 6
  • 19
  • 43
0

When you read from xml, you should be able to cast to VARCHAR datatype and INSERT, UPDATE accordingly.

Sample code below:

DECLARE @table table(quotedstring varchar(100))
DECLARE @xml xml = 
'<root><t>this is PL SQL&apos;s quoted literal</t><t>this is second PL SQL&apos;s quoted literal</t></root>'

insert into @table
select t.value('t[1]','varchar(100)') as qstring from @xml.nodes('root') as x(t) 

UPDATE @table
SET quotedstring = (SELECT t.value('t[2]','varchar(100)') from @xml.nodes('root') as x(t))

SELECT * from @table
quotedstring
this is second PL SQL's quoted literal
Venkataraman R
  • 12,181
  • 2
  • 31
  • 58
  • Erm, you can just double up the quotes `''` you don't *need* to use `&pos;`. And OP's issue is actually related to escaping the XML literal in the first place, either with `''` or with `&pos;` – Charlieface Jan 10 '22 at 04:50
  • @Charlieface, as OP is having single quote inside XML file, I think he should be having it as `'`, otherwise, xml will not be valid. So, I thought the way he reads xml, he can cast it and it should be fine. – Venkataraman R Jan 10 '22 at 06:00
  • Looks perfectly valid to me to just double up quotes, see https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=3caf1bd76024b6cb5d1f6ca6ac7ff75a. And as I said, it sounds like OP wants to know how to have a literal *without* escaping things, which isn't possible in T-SQL – Charlieface Jan 10 '22 at 09:56
  • @Charlieface, ok. got it. thanks. – Venkataraman R Jan 10 '22 at 10:44
-1

In T-SQL you can have two single coats together to exclude single coats (no double coat).

for example:

'this is PL SQL's quoted literal'

Should change as follows:

'this is PL SQL''s quoted literal'
alireza hashemi
  • 16
  • 2