Join or sign in Register for your free TechRepublic membership or if you are already a member, sign in using your preferred method below. Use Your Email Use FacebookUse Linkedin

XML -> SQL Server problem

Locked

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!

Topic

How is it treating the tags?

In reply to XML -> SQL Server problem

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?

A couple of options

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

Arg

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.

[Author]

Replies