replace XMLNode in a XMLString Column of a table in SQL Server

Question

I want to delete xmlnode <responseDeclaration> whose attribute "identifier" value is not "RESPONSE" from table "Rubric"; which has column "XMLString" of type String and "XMLNode" column of type Xml.Before deleting the node "<responseDeclaration>" we need Outerxml of the node which I am deleting into a temp variable; which in this case is

 "<responseDeclaration identifier="VH182077_zone_choice_list_9.RESPONSE" baseType="identifier" cardinality="single" />"

Below is the content for both the columns "XMLNode" and "XMLString"

<assessmentItem xmlns:xi="http://www.w3.org/2001/XInclude" xmlns="http://www.imsglobal.org/xsd/imsqti_v2p2"  timeDependent="false" title="ZonesSS" toolVersion="1.0" toolName="Rubric">
  <responseDeclaration baseType="identifier" cardinality="single" identifier="RESPONSE">
    <correctResponse>
      <value>i3</value>
    </correctResponse>
  </responseDeclaration>
  <responseDeclaration identifier="VH182077_zone_choice_list_9.RESPONSE" baseType="identifier" cardinality="single" />
  <outcomeDeclaration baseType="float" cardinality="single" identifier="SCORE">
    <defaultValue>
      <value>0</value>
    </defaultValue>
  </outcomeDeclaration>
  <responseProcessing template="http://www.imsglobal.org/question/qti_v2p1/rptemplates/match_correct_1.xml" />
</assessmentItem>

Below is the result I am looking for in both the columns. we do have these kind of records in the "Rubric" Table.

<assessmentItem xmlns:xi="http://www.w3.org/2001/XInclude" xmlns="http://www.imsglobal.org/xsd/imsqti_v2p2" identifier="3c6e5f58-bd87-e511-ba0c-005056a8223d" timeDependent="false" title="ZonesSS" toolVersion="1.0" toolName="IAT Rubric">
  <responseDeclaration baseType="identifier" cardinality="single" identifier="RESPONSE">
    <correctResponse>
      <value>i3</value>
    </correctResponse>
  </responseDeclaration>
  <outcomeDeclaration baseType="float" cardinality="single" identifier="SCORE">
    <defaultValue>
      <value>0</value>
    </defaultValue>
  </outcomeDeclaration>
  <responseProcessing template="http://www.imsglobal.org/question/qti_v2p1/rptemplates/match_correct_1.xml" />
</assessmentItem>

Tried to get string value of node which i want to delete with below query; but not successful.

 Declare @RDNode as 
    ;WITH XMLNAMESPACES 
    ('http://www.imsglobal.org/xsd/imsqti_v2p2' AS ns)
   SET @RDNode= select cast(XmlString as xml).value('(/ns:assessmentItem/ns:responseDeclaration[2])[1]', 'nvarchar(max)')
    FROM dbo.Rubric R WHERE R.[XML].exist('(/ns:assessmentItem/ns:responseDeclaration[2]/@identifier[contains(.,".RESPONSE")])')=1
    WHERE ..

Tried to delete second ResponseDeclaration Node from 'XMLNode'

;WITH XMLNAMESPACES 
('http://www.imsglobal.org/xsd/imsqti_v2p2' AS ns)
Update dbo.Rubric 
SET [XMLNode].modify('replace value of (/ns:assessmentItem/ns:responseDeclaration[2])[1] with ""')
WHERE ....

har07 · Accepted Answer · 2015-12-05 04:43:38Z

1

"I want to delete xmlnode whose attribute "identifier" value is not "RESPONSE"..."

Use delete keyword instead of replace value of to delete node(s) from XML data type :

;WITH XMLNAMESPACES ('http://www.imsglobal.org/xsd/imsqti_v2p2' AS ns)
Update dbo.Rubric
SET [XMLNode].modify('
    delete /ns:assessmentItem/ns:responseDeclaration[@identifier != "RESPONSE"]
')

And use query() method instead of value() to get the XML markup that will be deleted :

Declare @RDNode as XML
;WITH XMLNAMESPACES ('http://www.imsglobal.org/xsd/imsqti_v2p2' AS ns)
select @RDNode =  R.[XMLNode].query('/ns:assessmentItem/ns:responseDeclaration[@identifier != "RESPONSE"]')
FROM dbo.Rubric R 
WHERE ....

edited Dec 5, 2015 at 4:43

answered Dec 5, 2015 at 4:38

har07

89.5k12 gold badges87 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chat Over a year ago

How we delete the namespace "xmlns:xi="w3.org/2001/XInclude" from above <assessmentItem> root node.

Collectives™ on Stack Overflow

replace XMLNode in a XMLString Column of a table in SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related