How to get CDATA from XML file (with query)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to get CDATA from XML file (with query)

Author

Topic

jrnordi
Starting Member

2 Posts

Posted - 2012-10-24 : 17:36:55

Hi Guys!

I'm trying to get the SalesOrder and SalesOrderLine data from the XML that contains CDATA.

Follow my query:

BEGIN TRAN
DECLARE @Response xml

SET @Response =
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<CreateOrderConfigurationsResponse xmlns="http://services.gem.emc.com/DSI/Svc/ProductData">
<CreateOrderConfigurationsResult>
<ConfigResponse>
<salesOrder>3452</salesOrder>
<salesOrderLine>10</salesOrderLine>
<workOrder>1234</workOrder>
<workOrderLine>2</workOrderLine>
<errorToken>0</errorToken>
<configDocument><![CDATA[<?xml version="1.0" encoding="utf-16"?>
<ConfigurationSpec xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" salesOrder="0023945465" salesOrderLine="10" inputItemCount="473" placedItemCount="7" unplacedItemCount="466" xmlns="http://services.gem.emc.com/DSI/Docs/ProductConfig_v1">
<ConfiguredParts salesOrder="0023945465" salesOrderLine="10" configHash="0768D10CD68B84EB19B7FB6A6CE802E5">
<Placement partNumber="100-563-783" configLevel="CTO" hashOrder="0" source="INPUT" location="" hierarchyHash="9038F56D207E8FCAFCBB4076E1BD1F16">
<Placement partNumber="100-563-682" configLevel="BTS" hashOrder="0" source="INPUT" location="" />
<Placement partNumber="100-563-736" configLevel="CTO" hashOrder="1" source="INPUT" location="CHA_DIMM1" />
<Placement partNumber="100-563-798" configLevel="CTO" hashOrder="2" source="INPUT" location="SLIC0" />
<Placement partNumber="100-563-795" configLevel="CTO" hashOrder="3" source="INPUT" location="SLIC1" />
<Placement partNumber="100-563-796" configLevel="CTO" hashOrder="4" source="INPUT" location="SLIC2" />
<Placement partNumber="100-563-786" configLevel="CTO" hashOrder="5" source="INPUT" location="SLIC3" />
</Placement>
</ConfiguredParts>
<UnplacedParts>
<Item partNumber="501-0099-0001" />
<Item partNumber="501-0099-0001" />
<Item partNumber="C-10G-S2PO" />
<Item partNumber="C-10G-S2PO" />
<Item partNumber="C-990XMEM" />
<Item partNumber="463-0157-0001" />
<Item partNumber="465-0102-0001" />
<Item partNumber="DDRACK-40U" />
<Item partNumber="DDRACK-40U" />
<Item partNumber="DDRACK-40U" />
<Item partNumber="463-0012-0001" />
</UnplacedParts>
</ConfigurationSpec>]]></configDocument>
</ConfigResponse>
</CreateOrderConfigurationsResult>
</CreateOrderConfigurationsResponse>
</soap:Body>
</soap:Envelope>'

Declare @xmlPlacement xml
Declare @PlacementTable TABLE (ID INT IDENTITY(1,1) PRIMARY KEY, XMLPlacement XML)
Declare @looper int
Declare @PlacementNumber int
Declare @ProcessedStatusID int
Declare @ErrorStatusID int

Declare @SalesOrder varchar(100)
Declare @SalesOrderLine varchar(100)
Declare @DSIRequestID int
Declare @rc int

SET @looper = 1
SET @PlacementNumber = 0
SET @DSIRequestID = 0
SET @rc = 0

select @ProcessedStatusID = ID from udtDSIRequestStatus where Description = 'Processed'
select @ErrorStatusID = ID from udtDSIRequestStatus where Description = 'Error'

--get DSIRequest
SELECT
Parent.Elm.value('ConfigurationSpec[1]/ConfiguredParts[1]/salesOrder[0]', 'varchar(100)') AS SALESORDER,
Parent.Elm.value('ConfigurationSpec[1]/ConfiguredParts[1]/salesOrderLine[0]', 'varchar(100)') AS SALESORDERLINE
FROM
@Response.nodes('/Envelope/Body/CreateOrderConfigurationsResponse/CreateOrderConfigurationsResult/ConfigResponse/configDocument') AS Parent(Elm)

ROLLBACK TRAN

Please, can someone help me to figure it out?

Thanks.

Norberto Nordi Junior

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-25 : 06:37:42

You cannot query CDATA the way you are doing - to SQL, it is just some text.
So you have to extract that text, cast it as XML and then query it.

Also, you need to indicate the namespaces against which you are querying. So your query to get the SalesOrder and SalesOrderLine should be like shown below. I didn't quite follow what the rest of the query (@looper, udtDSIRequestStatus etc.) is doing.

;WITH XMLNAMESPACES (
	'http://services.gem.emc.com/DSI/Svc/ProductData' as ns1,
	'http://services.gem.emc.com/DSI/Docs/ProductConfig_v1' AS ns2
)
,cte AS
(
	SELECT
		CAST(c.value('(ns1:configDocument/text())[1]','nvarchar(max)') AS XML) AS configDocument
	FROM
		@Response.nodes('//ns1:ConfigResponse') T(c)
) 
SELECT
	configDocument.value('(ns2:ConfigurationSpec/ns2:ConfiguredParts/@salesOrder)[1]',
		'varchar(100)') AS SALESORDER,
	configDocument.value('(ns2:ConfigurationSpec/ns2:ConfiguredParts/@salesOrderLine)[1]',
		'varchar(100)') AS SALESORDERLINE
FROM
	cte;