insert (xml dml) error

• mssqlnoob

  Ten Centuries

  Points: 1245

  More actions

  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

• Christian Graus

  SSC Eights!

  Points: 963

  More actions

  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 ?

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  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