Post reply

insert (xml dml) error

  • January 6, 2014 at 9:10 pm

    #280925

    from

    http://technet.microsoft.com/en-us/library/ms175466.aspx

    USE AdventureWorks2012;

    GO

    DECLARE @myDoc xml

    SET @myDoc =

    '<Root>

    <ProductDescription ProductID="1" ProductName="Road Bike">

    <Features> </Features>

    </ProductDescription>

    </Root>'

    SELECT @myDoc

    SET @myDoc.modify('

    insert <![CDATA[ <notxml> as text </notxml> or cdata ]]>

    into (/Root/ProductDescription/Features)[1] ')

    SELECT @myDoc ;

    GO

    Msg 2209, Level 16, State 1, Line 10

    XQuery [modify()]: Syntax error near '<!'

    May I know how should I resolve this error?

    thanks

  • January 6, 2014 at 9:22 pm

    #1677935

    The example is obviously broken. I wonder what the use of inserting a CDATA section in to XML, in SQL is, any how ?

  • January 8, 2014 at 12:29 pm

    #1678546

    Unless I am mistaken it appears that you cannot add CDATA to the XML data type. This is a known Microsoft bug which they doesn't look like it's getting fixed.

    http://connect.microsoft.com/SQLServer/feedback/details/298860/xml-cdata-tag-is-lost-when-converting-to-xml-data-type

    This thread shows you how to do this if you don't use the XML data type.

    I am not sure what you are trying to do here but I think you can do it without inserting CData (e.g. create an element/tag called "notxml".

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply