Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Nested XML/CDATA ... Expand / Collapse
Author
Message
Posted Friday, February 1, 2013 6:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 7, 2014 7:35 AM
Points: 20, Visits: 92
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

Post #1414614
Posted Friday, February 1, 2013 6:51 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:33 AM
Points: 2,580, Visits: 1,622
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.

Post #1414652
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse