Inserting XML

Question

Post reply

Inserting XML

Viewing 7 posts - 31 through 36 (of 36 total)

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

Revenant SSC-Forever Points: 42467 More actions · Answer 1

It's good to be on the Pacific Daylight Time: QotD is already straightened out when I get to see it. 😉

Thanks, Steve!

Ken SSCoach Points: 17191 More actions · Answer 2

Glad I got to this one late so I avoided the confusion. Thanks for fixing that Steve.

tom.w.brannon Hall of Fame Points: 3976 More actions · Answer 3

I don't know anything about XML but the following works just fine as well:

CREATE TABLE #Mytest2 (id INT, x XML)

INSERT #mytest2

VALUES

(11, CAST('<customer id = "1200">Acme</customer>' AS XML))

, (21, CAST('<customer id = "1300">Consolidated</customer>' AS XML))

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 4

tom.w.brannon (6/1/2016)
I don't know anything about XML but the following works just fine as well:
CREATE TABLE #Mytest2 (id INT, x XML)
INSERT #mytest2
VALUES
(11, CAST('<customer id = "1200">Acme</customer>' AS XML))
, (21, CAST('<customer id = "1300">Consolidated</customer>' AS XML))

Yes, that would work. The version now in the QotD is missing the quotation marks around the id, hence the error and the answer.

Cheers!

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 5

Even two single quotes will also work fine.

INSERT mytest

VALUES

(1, CAST('<customer id = ' '1200' '>Acme</customer>' AS XML))

, (2, CAST( '<customer>Consolidated</customer>' AS XML))

Thanks.

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 6

SQL-DBA-01 (6/1/2016)
Even two single quotes will also work fine.
INSERT mytest
VALUES
(1, CAST('<customer id = ' '1200' '>Acme</customer>' AS XML))
, (2, CAST( '<customer>Consolidated</customer>' AS XML))

Out of curiosity, how and where does this syntax work?

😎

The space between the single quotes makes the xml string invalid as a string let alone xml.

g.maxfield Default port Points: 1471 More actions · Answer 7

I too agree - I didn't get an error also so was perplexed at the wording of the question. However, it did make me think about the reasoning behind the question and potential answers which made me investigate deeper into requirements for xml so thanks;-)