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

XML + SOAP Expand / Collapse
Author
Message
Posted Tuesday, May 11, 2010 3:16 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 28, 2013 1:51 AM
Points: 17, 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
Post #920046
Posted Tuesday, May 11, 2010 3:34 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 28, 2013 1:51 AM
Points: 17, 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)

Post #920056
Posted Sunday, October 14, 2012 6:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, October 14, 2012 6:52 PM
Points: 1, 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
Post #1372549
Posted Monday, October 15, 2012 2:49 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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


Post #1372945
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse