Nested XML/CDATA ...

  • 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

  • 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.

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

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