InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 13:38:53 GMT20http://www.sqlservercentral.com/Forums/Topic1414614-21-1.aspxHi,Personally, if I was to approach this from the SQL server side of things, I would do something like the following. It isn't pretty, but it works and I hope that someone could post a better solution here :-D[code="sql"]DECLARE @xml XMLSET @xml = '<DOWNLOADED-SCRIPT SCRIPTID="blah" OBJECTTYPE="IS"> <SCRIPT-MAIN> <![CDATA[ <?xml version="1.0" encoding="utf-8"?> <Script ScriptId="blah" StartNode="600001" LastModifiedBy="xxxxxx" BeginDate=""> <Node NodeId="600001" TemplateId="Dialogue" X="595" Y="1215" Column="-1" Row="-1" NodeType="template"> <Resource URI="Package_Type" Name="Survey"/> <NodeDescription Language="en" Text="Package Type?"/> <NodeDescription Language="cs" Text=""/> </Node> <Node ChapterId="1" NodeId="600002" TemplateId="Dialogue" X="5982" Y="2407" Column="-1" Row="-1" NodeType="template"> <Resource URI="Fault_Type?" Name="Survey"/> <NodeDescription Language="en" Text="Fault Type?"/> </Node> </Script> ]]> </SCRIPT-MAIN></DOWNLOADED-SCRIPT>'--Extract xml in cdata sectionDECLARE @InnerXmltext VARCHAR(MAX)SELECT @InnerXmltext = LTRIM(@xml.value('(//SCRIPT-MAIN/text())[1]', 'varchar(max)'))--Get declaration at start of stringSET @InnerXmltext = SUBSTRING(@InnerXmltext, CHARINDEX('<', @InnerXmltext), LEN(@InnerXmltext))--cast string to xmlDECLARE @InnerXml XMLSET @InnerXml = CAST(@InnerXmltext AS XML)SELECT @InnerXml[/code]The idea is that the query first extracts the contents of the cdata section into a varchar(max) variable. Then the code trims of the padding at the start of the @InnerXmltext so that the xml declaration is at the start of the text. This should make the contents of @InnerXmltext valid and able to cast to xml so that you can start to use the xml functions against it.Fri, 01 Feb 2013 06:51:31 GMTarthurolcothttp://www.sqlservercentral.com/Forums/Topic1414614-21-1.aspxI've been kindly given some XML, and asked to return relevant values from within.Now I have only done simple xml before, and this one has me beat.It seams to have a second xml document nested within one of the nodes wrapped by a CDATA tag.I have a sneaking feeling that whoever write the application that produces this xml, should have provided us with the facility to read it ! .. but regardless, this is the situation as of today.Could anyone point me in the right direction to being able to decipher this and strip/display the node values?Many Thanks[code]<?xml version="1.0" encoding="utf-8"?><DOWNLOADED-SCRIPT SCRIPTID="blah" OBJECTTYPE="IS"> <SCRIPT-MAIN> <![CDATA[ <?xml version="1.0" encoding="utf-8"?> <Script ScriptId="blah" StartNode="600001" LastModifiedBy="xxxxxx" BeginDate=.....<truncated for post> <Node NodeId="600001" TemplateId="Dialogue" X="595" Y="1215" Column="-1" Row="-1" NodeType="template"> <Resource URI="Package_Type" Name="Survey"/> <NodeDescription Language="en" Text="Package Type?"/> <NodeDescription Language="cs" Text=""/> </Node> <Node ChapterId="1" NodeId="600002" TemplateId="Dialogue" X="5982" Y="2407" Column="-1" Row="-1" NodeType="template"> <Resource URI="Fault_Type?" Name="Survey"/> <NodeDescription Language="en" Text="Fault Type?"/>... etc etc[/code]Fri, 01 Feb 2013 06:00:17 GMTSim-473257