XML -> SQL Server problemLocked
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
FROM OPENXML(@hdoc,’/root/contentpackage/contentitem’, 2)
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!