General discussion

  • Creator
  • #2307296

    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:
    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!

All Comments

  • Author
    • #3360590

      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

      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?

    • #3606541

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

      • #3606539


        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.

Viewing 1 reply thread