2

My data is saved in SQL Server 2014 in xml format, and in SOAPUI JDBC request it is coming in this format

<Results>
<ResultSet fetchSize="128">
<Row rowNumber="1">
  <ConfigurationXML><![CDATA[<ROOT><Device>[{"Name":"AL","Profile":{"ID":1,"Height":240},"Index":-1}]</Device></ROOT>]]></ConfigurationXML> 
</Row>
</ResultSet>
</Results>

I want to compare this data with my REST Json Response, But how can I fetch data from this CDATA

Rao
  • 20,781
  • 11
  • 57
  • 77
Gkm
  • 237
  • 1
  • 5
  • 19

3 Answers3

2

The sample jdbc response provided by is little complex. Needs to be retried in multiple phases based on the nature of the data.

  • Response xml has CDATA
  • CDATA again has Xml
  • That xml has json

Please find the below groovy script to retried final json and retrieving its properties as shown below.

Script is done with appropriate comments in line.

import com.eviware.soapui.support.XmlHolder
import groovy.json.*
//Sample response
def response = '''<Results>
<ResultSet fetchSize="128">
<Row rowNumber="1">
  <ConfigurationXML><![CDATA[<ROOT><Device>[{"Name":"AL","Profile":{"ID":1,"Height":240},"Index":-1}]</Device></ROOT>]]></ConfigurationXML> 
</Row>
</ResultSet>
</Results>'''
//Create the xml holder object for the jdbc response
def holder = new XmlHolder(response)
//Get ConfigurationXML Node value using Xpath from holder object, which will retrive CDATA
def configurationXmlCdata =  holder.getNodeValue('//*:ConfigurationXML')
log.info "Configruation Xml Cdata: ${configurationXmlCdata}"
//There is again xml inside CDATA, so create xml holder object to retried json string
def cdataHolder = new XmlHolder(configurationXmlCdata)
def jsonString = cdataHolder.getNodeValue('//ROOT/Device')
log.info "Device Json data: ${jsonString}"
//Create Json slurper object if you need to access json
def jsonData = new JsonSlurper().parseText(jsonString)
log.info jsonData.Name
log.info jsonData.Index
log.info jsonData.Profile.ID

enter image description here

Rao
  • 20,781
  • 11
  • 57
  • 77
0

I tried to convert XML to string then replace characters and finally cast the replaced character expression back to XML

declare @xml xml = '
<Results>
<ResultSet fetchSize="128">
<Row rowNumber="1">
  <CONFIGURATIONXML>&lt;ROOT&gt;&lt;/Device&gt;[{"Name":"AL","Profile":{"ID":1,"Height":240},"Index":-1}]&lt;/ROOT&gt;</CONFIGURATIONXML> 
</Row>
</ResultSet>
</Results>
'

select replace(replace( CAST(@xml as nvarchar(max)),'&lt;','<'),'&gt;','>')
    -- CAST( replace(replace( CAST(@xml as nvarchar(max)),'&lt;','<'),'&gt;','>') as xml)

But it fails because of the DEVICE tag It should be in

Eralper
  • 6,461
  • 2
  • 21
  • 27
  • This is not necessary... Reading from XML will replace escaped characters implicitly. You might see my answer... – Shnugo Aug 12 '16 at 09:57
0

Reading the value properly will de-code the characters implicitly.

But: Your inner XML is not valid!:

DECLARE @xml XML=
'<Results>
  <ResultSet fetchSize="128">
    <Row rowNumber="1">
      <CONFIGURATIONXML>&lt;ROOT&gt;&lt;/Device&gt;[{"Name":"AL","Profile":{"ID":1,"Height":240},"Index":-1}]&lt;/ROOT&gt;</CONFIGURATIONXML>
    </Row>
  </ResultSet>
</Results>';
SELECT @xml

SELECT @xml.value('(/Results/ResultSet/Row/CONFIGURATIONXML)[1]','nvarchar(max)')

Leads to:

<ROOT></Device>[{"Name":"AL","Profile":{"ID":1,"Height":240},"Index":-1}]</ROOT>

As you can see, the </DEVICE> is a closing tag, but there's no opening tag for this...

Don't know how this is created... If this is always the same, you could repair this like this:

SELECT CAST(REPLACE(@xml.value('(/Results/ResultSet/Row/CONFIGURATIONXML)[1]','nvarchar(max)'),'</Device>','') AS XML).value('/ROOT[1]','varchar(max)')

The result

[{"Name":"AL","Profile":{"ID":1,"Height":240},"Index":-1}]
Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • Thanks for your help, but there is some misunderstanding , may be my question is not as clear , actually the data is coming in SOAPUI JDBC request, and I want to compare it with my json response values, and for this I have to write a groovy script but my question is how can I extract the data which is enclosed in CONFIGURATIONXML – Gkm Aug 12 '16 at 11:01
  • @Geeta, Isn't this exactly what the first code-block does? My *Leads to* is the result fo the first `SELECT`, which is the content of the given element... – Shnugo Aug 12 '16 at 11:20
  • yes it works but it is not working on my actual data as I want, it combines CDATA string and remove all tags – Gkm Aug 12 '16 at 12:16
  • Please avoid chameleon-questions: My answer really answered your question as it was given before. This is now a new issue, because reading (and **getting it out**) of a `CDATA`-section is a completely new issue. In short: This is not possible. The `CDATA` will get lost with XML approaches. But you might just add it like here: http://stackoverflow.com/a/38547537/5089204 – Shnugo Aug 12 '16 at 12:23