Software

General discussion

Gravatar
0 Votes
Locked

XML -> SQL Server problem

By SmithfromCambridge ·
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!

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

gravatar
Collapse -

How is it treating the tags?

by MonkeyButter In reply to XML -> SQL Server problem

I'm not entirely hot on XML, but could it be treating the <p> 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 <p> form before it gets put in XML format. Perhaps replacing them with ascii characters for two carriage returns?

gravatar
Collapse -

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&gt.

gravatar
Collapse -

Arg

by Fragmented In reply to A couple of options

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

Hopefully that will come out better.

Back to Software Forum

Start or search

Related Discussions

Related Forums