SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


XML + SOAP


XML + SOAP

Author
Message
fayilt
fayilt
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 222
Can you guys help me with this XML?


if object_id('tempdb.dbo.#Temp') is not null
drop table dbo.#Temp


CREATE TABLE #Temp (col xml)


INSERT #Temp (col)
select '<?xml version="1.0"?>
<soap:Envelope
xmlns:soap="http://www.w3.org/2001/12/soap-envelope"
soap:encodingStyle="http://www.w3.org/2001/12/soap-encoding">
<soap:Body xmlns:m="http://www.example.org/stock">
<m:GetStockPrice>
<m:StockName>0.018</m:StockName>
</m:GetStockPrice>
</soap:Body>
</soap:Envelope>'

select T.c.value('m:StockName[1]','decimal(8,3)')
from #Temp
cross apply col.nodes('soap:Envelope/soap:Body/m:GetStockPrice') AS T(c)



I keep getting "The name "soap" does not denote a namespace." error.

How do I access nodes with namespaces?

Thanks
fayilt
fayilt
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 222
Sorry, I found out...


if object_id('tempdb.dbo.#Temp') is not null
drop table dbo.#Temp


CREATE TABLE #Temp (col xml)


INSERT #Temp (col)
select '<?xml version="1.0"?>
<soap:Envelope
xmlns:soap="http://www.w3.org/2001/12/soap-envelope"
soap:encodingStyle="http://www.w3.org/2001/12/soap-encoding">
<soap:Body xmlns:m="http://www.example.org/stock">
<m:GetStockPrice>
<m:StockName>0.018</m:StockName>
</m:GetStockPrice>
</soap:Body>
</soap:Envelope>';WITH XMLNAMESPACES ( 'http://www.w3.org/2001/12/soap-envelope' as "soap", 'http://www.example.org/stock' as "m")

select T.c.value('m:StockName[1]','decimal(8,3)')
from #Temp
cross apply col.nodes('soap:Envelope/soap:Body/m:GetStockPrice') AS T(c)


mob.zanotti
mob.zanotti
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 0
Hi.

How can i parse this xml (I invoke a webservice and i got this return):

<ns:somaResponse xmlns:ns="http://sum.com">
<ns:return>3</ns:return>
</ns:somaResponse>

I used this example (from this post), but doesn't work.
Anyone can help me ?

Thanks
Steven Willis
Steven Willis
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2047 Visits: 1721
Here's a stored procedure that you can use to parse XML. The first example inside the procedure is the XML posted by the original OP. I've included a more comprehensive example so one can see how nodes, properties, and data are parsed into a table.

This procedure was designed to run basically one XML input at a time so it may not be optimally efficient when used to parse large batches with large amounts of data. In such case I'd recommend using XQUERY. There's lots of articles on XQUERY on this site. But for a basic XML parser, try this:



CREATE PROCEDURE dbo.ParseXML

@strXML NVARCHAR(MAX)
,@schemanode NVARCHAR(255)
,@rootnode NVARCHAR(255)

AS
BEGIN

/*
SAMPLE PARAMETERS

EXEC dbo.ParseXML
N'<somaResponse xmlns:ns="http://sum.com"><ns:return>3</ns:return></somaResponse>'
,'root'
,'/somaResponse'

EXEC dbo.ParseXML
N'<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soapenv:Body>
<Reports xmlns="http://www.sample.com/schemas">
<ReportWrapper type="report">
<TransactionID>123456789</TransactionID>
<ReportHeader>
<ReportName />
<DistrictContacts>
<District ContactName="North">George Washington</District>
<District ContactName="South">John Adams</District>
<District ContactName="East">Thomas Jefferson</District>
<District ContactName="West">James Madison</District>
</DistrictContacts>
</ReportHeader>
<Management>
<ManagerName type="subject">
<Prefix>Mr</Prefix>
<FirstName>John</FirstName>
<SurName>Dilinger</SurName>
<Suffix />
</ManagerName>
<DemographicDetail>
<GovernmentId issuingAuthority="SSN">111-11-1111</GovernmentId>
<DateOfBirth>02/03/1984</DateOfBirth>
<Gender>M</Gender>
</DemographicDetail>
</Management>
</ReportWrapper>
</Reports>
</soapenv:Body>
</soapenv:Envelope>'
,'Reports'
,'/ReportWrapper'

*/

SET NOCOUNT ON

DECLARE
@idoc INT
,@id INT
,@parentid INT
,@SoapEnvOpen NVARCHAR(MAX)
,@SoapEnvClose NVARCHAR(MAX)
,@SoapBodyOpen NVARCHAR(MAX)
,@SoapBodyClose NVARCHAR(MAX)
,@SchemaNodeOpen NVARCHAR(MAX)
,@SchemaNodeClose NVARCHAR(MAX)
,@checkSoap INT
,@checkSchema INT
,@checkXMLHeader INT
,@XMLHeader NVARCHAR(MAX)


IF OBJECT_ID('tempdb..#ChildList') IS NOT NULL
DROP TABLE #ChildList

CREATE TABLE #ChildList (
[RowNum] INT IDENTITY(1,1) NOT NULL,
[parentid] INT NULL,
[id] INT NULL,
PRIMARY KEY (RowNum),
UNIQUE (RowNum))


IF OBJECT_ID('tempdb..#NodeList') IS NOT NULL
DROP TABLE #NodeList

CREATE TABLE #NodeList (
[RowNum] INT NOT NULL,
[id] INT NULL,
[parentid] INT NULL,
[nodetype] INT NULL,
[localname] NVARCHAR(MAX) NULL,
[text] NVARCHAR(MAX) NULL,
PRIMARY KEY (RowNum),
UNIQUE (RowNum))


SET @id = 1
SET @parentid = NULL


/* Check to see if any XML exists */

SET @strXML = NULLIF(@strXML,'')

IF @strXML IS NULL
BEGIN

SELECT
NULL AS RowNum
,NULL AS id
,NULL AS parentid
,NULL AS nodepath
,NULL AS nodetype
,NULL AS nodename
,NULL AS property
,NULL AS value
,NULL AS nodecontents
,'No XML to process' AS XMLStatus

RETURN

END
ELSE
BEGIN

-- Get rid of tabs, extra spaces, and other extraneous characters

IF CHARINDEX('<',@strXML,1) > 0
BEGIN
SET @strXML = REPLACE(@strXML,'<','<')
SET @strXML = REPLACE(@strXML,'>','>')
END

SET @strXML = REPLACE(@strXML,CHAR(9),' ') -- convert tabs to spaces

SET @strXML =
REPLACE(
REPLACE(
REPLACE(
@strXML
,' ',' '+CHAR(7))
,CHAR(7)+' ','')
,CHAR(7),'')

SET @strXML = REPLACE(@strXML,'> <','><')

END


/* Check to see if the XML has a header */

SET @checkXMLHeader = CHARINDEX('<?xml version',@strXML,0)


/* If the XML has a header then remove it */

IF @checkXMLHeader > 0
BEGIN
SET @XMLHeader = SUBSTRING(@strXML,CHARINDEX('<?xml version',@strXML,0),CHARINDEX('>',@strXML,0))
SET @strXML = REPLACE(@strXML,@XMLHeader,'')
END


/* Check to see if the XML has a SOAP wrapper */

SET @checkSoap = CHARINDEX('<soapenv:Envelope',@strXML,0)


/* If the XML has a SOAP wrapper then remove it */

IF @checkSoap > 0
BEGIN
SET @SoapEnvOpen = SUBSTRING(@strXML,CHARINDEX('<soapenv:Envelope',@strXML,0),CHARINDEX('>',@strXML,0))
SET @strXML = REPLACE(@strXML,@SoapEnvOpen,'')
SET @SoapBodyOpen = SUBSTRING(@strXML,CHARINDEX('<soapenv:Body',@strXML,0),CHARINDEX('>',@strXML,0))
SET @strXML = REPLACE(@strXML,@SoapBodyOpen,'')
SET @SoapEnvClose = SUBSTRING(@strXML,CHARINDEX('</soapenv:Envelope>',@strXML,0),LEN('</soapenv:Envelope>'))
SET @strXML = REPLACE(@strXML,@SoapEnvClose,'')
SET @SoapBodyClose = SUBSTRING(@strXML,CHARINDEX('</soapenv:Body>',@strXML,0),LEN('</soapenv:Body>'))
SET @strXML = REPLACE(@strXML,@SoapBodyClose,'')
END


/* Check to see if the XML has a schema definition node */

SET @checkSchema = CHARINDEX('<'+@schemanode,@strXML,0)


/* If a schema definition node exists remove it */

IF @checkSchema > 0
BEGIN

SET @SchemaNodeOpen = SUBSTRING(@strXML,CHARINDEX('<'+@schemanode,@strXML,0),CHARINDEX('>',@strXML,0))
SET @strXML = REPLACE(@strXML,@SchemaNodeOpen,'')
SET @SchemaNodeClose = SUBSTRING(@strXML,CHARINDEX('</'+@schemanode+'>',@strXML,0),LEN('</'+@schemanode+'>'))
SET @strXML = REPLACE(@strXML,@SchemaNodeClose,'')

END


BEGIN TRY
EXEC sp_xml_preparedocument @idoc OUTPUT, @strXML
END TRY
BEGIN CATCH
BEGIN

SELECT
NULL AS RowNum
,NULL AS id
,NULL AS parentid
,NULL AS nodepath
,NULL AS nodetype
,NULL AS nodename
,NULL AS property
,NULL AS value
,NULL AS nodecontents
,'Invalid XML' AS XMLStatus

RETURN

END
END CATCH


/* Parse the XML to get the nodes */

;WITH cte
AS (
SELECT
CAST(p1.parentid AS INT) AS parentid
,CAST(p1.id AS INT) AS id
FROM
OPENXML (@idoc,@rootnode,2) AS p1
UNION ALL
SELECT
CAST(p2.parentid AS INT) AS parentid
,CAST(p2.id AS INT) AS id
FROM
OPENXML (@idoc,@rootnode,2) AS p2
JOIN
cte
ON CAST(cte.id AS INT) = CAST(p2.ParentID AS INT)
WHERE
CAST(p2.parentid AS INT) = @parentid
)
INSERT INTO #ChildList
SELECT *
FROM cte


/* Parse the nodes to get the data */

INSERT INTO #NodeList
SELECT
#ChildList.RowNum
,xmllist.id
,xmllist.parentid
,xmllist.nodetype
,xmllist.localname
,CAST(xmllist.[text] AS NVARCHAR(MAX)) AS [text]
FROM #ChildList
INNER JOIN
OPENXML (@idoc,@rootnode,2) AS xmllist
ON #ChildList.id = xmllist.id
WHERE
#ChildList.RowNum > 0


/* Display the formatted results */

;WITH RecursiveNodes(RowNum,id,parentid,nodepath,localname,[text],nodetype)
AS (
SELECT
#NodeList.RowNum
,#NodeList.id
,#NodeList.parentid
,CAST('/' + REPLACE(REPLACE(REPLACE(REPLACE(#NodeList.localname,'&',''),'?',''),' ',''),'.','') AS NVARCHAR(255)) AS nodepath
,#NodeList.localname
,CAST(#NodeList.[text] AS NVARCHAR(MAX)) AS [text]
,0 AS nodetype
FROM #ChildList
INNER JOIN
#NodeList
ON #ChildList.id = #NodeList.id
WHERE
#NodeList.parentid IS NULL
AND #ChildList.RowNum > 0
AND #NodeList.RowNum > 0
UNION ALL
SELECT
n.RowNum
,n.id
,n.parentid
,CAST(r.nodepath + '/'+ REPLACE(REPLACE(REPLACE(REPLACE(n.localname,'&',''),'?',''),' ',''),'.','') AS NVARCHAR(255)) AS nodepath
,n.localname
,n.[text]
,n.nodetype
FROM #NodeList AS n
INNER JOIN
RecursiveNodes AS r
ON n.parentid = r.id
WHERE
n.RowNum > 0
AND r.RowNum > 0
AND n.parentid >= 0
)
SELECT
ROW_NUMBER() OVER (ORDER BY Result.RowNum) AS RowNum
,Result.id
,Result.parentid
,Result.nodepath
,Result.nodetype
,Result.nodename
,Result.property
,Result.value
,Result.nodecontents
,'OK' AS XMLStatus
FROM
(
SELECT
rn.RowNum
,rn.id
,rn.parentid
,rn.nodepath
,(CASE
WHEN rn.nodetype = 0 THEN 'Root'
WHEN rn.nodetype = 1 THEN 'Node'
WHEN rn.nodetype = 2 THEN 'Property'
ELSE 'Data'
END) AS nodetype
,(CASE
WHEN rn.nodetype = 0 THEN rn.localname
WHEN rn.nodetype = 1 THEN rn.localname
WHEN rn.nodetype = 2 THEN (SELECT TOP(1) localname FROM RecursiveNodes WHERE id = rn.parentid)
ELSE NULL
END) AS nodename
,(CASE
WHEN rn.nodetype = 2 THEN rn.localname
ELSE NULL
END) AS property
,(CASE
WHEN rn.nodetype = 2 THEN (SELECT TOP(1) [text] FROM RecursiveNodes WHERE parentid = rn.id)
ELSE NULL
END) AS value
,(CASE
WHEN rn.nodetype = 1 THEN (SELECT TOP(1) [text] FROM RecursiveNodes WHERE parentid = rn.id)
WHEN rn.nodetype = 2 THEN (SELECT TOP(1) [text] FROM RecursiveNodes WHERE parentid = rn.parentid and [text] is not null)
ELSE NULL
END) AS nodecontents
FROM
RecursiveNodes AS rn
WHERE
rn.localname <> '#text'
) AS Result

END



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search