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

I want to delete xmlnode 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 I do need Outerxml of the node I want to delete; 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 ....    

No comments:

Post a Comment