<?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

DECLARE @xml XML
SET @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 section
DECLARE @InnerXmltext VARCHAR(MAX)
SELECT @InnerXmltext = LTRIM(@xml.value('(//SCRIPT-MAIN/text())[1]', 'varchar(max)'))

--Get declaration at start of string
SET @InnerXmltext = SUBSTRING(@InnerXmltext, CHARINDEX('<', @InnerXmltext), LEN(@InnerXmltext))

--cast string to xml
DECLARE @InnerXml XML
SET @InnerXml = CAST(@InnerXmltext AS XML)

SELECT @InnerXml