Software

Ask something
Discussion Locked

XML -> SQL Server problem

0 helpful
3 replies

by smithfromcambridge

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Really hoping someone can help me with this. I'm pretty new to both XML and SQL Server so I may be missing something really obvious.

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!

3 replies

by monkeybutter

I'm not entirely hot on XML, but could it be treating the

tags as XML tags, and ignoring them as they're not requested in your WITH clause?

If this is the case, you might want to look at curtailing the tags being in

form before it gets put in XML format. Perhaps replacing them with ascii characters for two carriage returns?

by fragmented

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>).

by fragmented

HTML formatting is always a pain - I meant my i.e. to read & plus lt plus ; then P then & plus gt plus ;

Hopefully that will come out better.

This discussion is closed to new replies.