Nested XML/CDATA ...

Question

Nested XML/CDATA ...

Sim-473257

Mr or Mrs. 500

Points: 532
More actions
February 1, 2013 at 6:00 am

#100504

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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Arthur Olcot SSCertifiable Points: 6676 More actions · Answer 1

Hi,

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 😀

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

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.