PROC SQL.. .How to insert the following XML as string?

[ChrisWoo]

I want to insert the XML as a string. May refer to the SAS code.

Not sure if there are double quote within the string.

proc sql;
    create table A (Requested_XML varchar(3000));

proc sql;
	insert into A values
"<?xml version="1.0"?><catalog>   <book id="bk101">      <author>Gambardella, Matthew</author>      <title>XML Developer's Guide</title>      <genre>Computer</genre>      <price>44.95</price>      <publish_date>2000-10-01</publish_date>      <description>An in-depth look at creating applications  with XML.</description>   </book>";

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant,a datetime constant,a missing value, ), +, ',', -, MISSING, NULL, USER.

ERROR 79-322: Expecting a (.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

ERROR: Bit Constants are not supported by SQL.

[Tom · Accepted solution]

To make a string literal in SAS you can use " or ' on the outside.  But if the character you want to use on the outside exists in the data you need to double it up.  

So if you string looks like:

<?xml version="1.0"?>

And you want to enclose it in " then you need to change it to:

<?xml version=""1.0""?>

Before adding the quotes.

"<?xml version=""1.0""?>"

[ChrisWoo]

i see.. TQVM

[Kurt_Bremser]

If you need to have double quotes in a string, use single quotes "on the outside".

[ChrisWoo]

Hi Kurt,

Any other way that I can do it without putting additional single / double quotes on every strings that contain quotes like "Chris"?

[Tom]

Leave the strings in data. 

For example read the string from a file:

data strings;
  input xml_string $200.;
datalines4;
<?xml version="1.0"?><catalog><book id="bk101"><author>Gambardella, Matthew</author><title>XML Developer's Guide</title><genre>Computer</genre><price>44.95</price><publish_date>2000-10-01</publish_date><description>An in-depth look at creating applications  with XML.</description></book>
;;;;

If you then want to insert that value into another dataset you can just use code instead of string literals.

proc sql;
insert into A (select xml_string from strings);

If you then need to use the data to generate code you can use the QUOTE() function.  For example you could put the quoted string into a macro variable.

data _null_;
  set strings;
  call symputx('mvar',quote(trim(xml_string),"'"));
run;
proc sql;
insert into A values (&mvar);

[ballardw]

Copy from the LOG the entire procedure with code and the error messages. On the forum open a text box and paste the entire log.

Typically the 22-322 error, and many others will place diagnostic characters into the log where the problem occurs so it helps to see that. Also the main message windows will reformat pasted text meaning that those diagnostic characters appear incorrectly if not pasted into a text box that preserves text format.

One strongly suspects it is related to multiple quotes, both single and double.

"<?xml version="1.0"?>

The second magenta " above would end the "value" treated as <?xml version=1

the 1. would then be expected to be another value but is not done correctly for insert.

Generally with SAS if you need to have double quote character in a value you could use a single quote around the value. But you also have a single quote in

XML Developer's Guide

Which will cause a similar issue. When you need quotes as part of a value then enter them twice. Also the Values expects a () around the value .

Try:

proc sql;
    create table A (Requested_XML varchar(3000));

proc sql;
	insert into A values
("<?xml version=""1.0""?><catalog>   <book id=""bk101"">      <author>Gambardella, Matthew</author>      <title>XML Developer''s Guide</title>      <genre>Computer</genre>      <price>44.95</price>      <publish_date>2000-10-01</publish_date>      <description>An in-depth look at creating applications  with XML.</description>   </book>");
quit;

Personally I would not use INSERT at all. A data step reading a text file is likely to be easier.