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