A couple of options
by Fragmented
In reply to
XML -> SQL Server problem
We've hit similar issues with HTML content, and we've come up with two solutions.
One is to enclose the element you're looking to import with a CDATA tag. The other is to perform proper HTML escaping of the data (i.e. <P>
.
XML -> SQL Server problem
I've got some XML files that I want to 'shred' and store in SQL Server. I'm using OPENXML to do this, and it pretty much works, except for the body fields of the xml. These contain paragraph tags, and when OPENXML imports the files, it seems to strip the paragraph tags out.
Here is a snippet of my code:
INSERT INTO XMLin
SELECT *
FROM OPENXML(@hdoc,'/root/contentpackage/contentitem', 2)
WITH (
publisher varchar(50) './contentmetadata/contributor[@type="publisher"]/orgref/@resource',
loccity char(20) './contentmetadata/subject/subjectlocation/location/city',
headline varchar (50) './head/headline',
body text './body')
(Hope the formatting comes out ok)
I figure I have to do something with that last line to tell OPENXML to just import the contents of the body element in "raw" form, but I've no idea how to do that.
Any suggestions?Thanks in advance for any help!
This conversation is currently closed to new comments.