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

FORXML not retrieving complete xml Expand / Collapse
Author
Message
Posted Tuesday, July 16, 2013 4:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 10:09 AM
Points: 189, Visits: 333
Hi,

I am having the below XML
<marketDataSeries xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://pricesandcurves.ist.bp.com/xsd/CSL/IST/marketData/v1" xsi:schemaLocation="http://pricesandcurves.ist.bp.com/xsd/CSL/IST/marketData/v1 marketData.xsd" seriesID="urn:pc:FO.E.34639">
<observationDate>2013-07-15T00:00:00</observationDate>
<timeZone>London +0 (+1)</timeZone>
<observationPeriod>
<createDateTime>2013-07-15T14:26:15Z</createDateTime>
<updateDateTime>2013-07-15T14:26:15Z</updateDateTime>
<deliveryPeriod id="urn:pc:35F461C251D56382E04400144F22F0C2">
<relativePeriodLabel>SPOT</relativePeriodLabel>
</deliveryPeriod>
<obsType>
<name>HIGH</name>
<obs>
<status>Normal</status>
<value>623.5</value>
<obsExtension>
<actorId>FAME</actorId>
</obsExtension>
</obs>
</obsType>
<obsType>
<name>LOW</name>
<obs>
<status>Normal</status>
<value>622.5</value>
<obsExtension>
<actorId>FAME</actorId>
</obsExtension>
</obs>
</obsType>
<obsType>
<name>MID</name>
<obs>
<status>Normal</status>
<value>623</value>
<obsExtension>
<actorId>FAME</actorId>
</obsExtension>
</obs>
</obsType>
</observationPeriod>
</marketDataSeries>

When I am trying to retrieve the data for the above xml from sql its just giving the first record only

EXEC sp_xml_preparedocument @DOCHANDLE OUTPUT, @XML,
'<root xmlns:myns="http://pricesandcurves.ist.bp.com/xsd/CSL/IST/marketData/v1" />'
SELECT @PRICECODE as EXPR1,(select PriceGeneratedDate from MI_ODS_CURVE_PRICES where id=@MAXID) as [EXPR2],
(select PriceReceivedDate from MI_ODS_CURVE_PRICES where id=@MAXID) as [EXPR3], *
FROM OPENXML (@DOCHANDLE, '/myns:marketDataSeries/myns:observationPeriod')
WITH (observationDate datetime '../myns:observationDate',
createDateTime datetime 'myns:createDateTime',
updateDateTime datetime 'myns:updateDateTime',
absolutePeriodLabel varchar(200) 'myns:deliveryPeriod/myns:absolutePeriodLabel',
deliveryPeriodBegin datetime 'myns:deliveryPeriod/myns:deliveryPeriodBegin',
deliveryPeriodEnd datetime 'myns:deliveryPeriod/myns:deliveryPeriodEnd',
name varchar(20) 'myns:obsType/myns:name',
status varchar(20) 'myns:obsType/myns:obs/myns:status',
value varchar(20) 'myns:obsType/myns:obs/myns:value',
actorId varchar(20) 'myns:obsType/myns:obs/myns:obsExtension/myns:actorId')

EXEC sp_xml_removedocument @DOCHANDLE

can anyone pls let me know what is problem in that

Thanks
Naveen
Post #1474058
Posted Tuesday, July 16, 2013 11:01 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
I'm not sure which direction you are trying to go with this. To return an XML document from a SQL query requires more data than you have provided (sample data and tables etc). But if you are trying to parse am XML document into SQL, here's a procedure that will do that for you (I added a root node but otherwise your XML is unchanged):


DECLARE @XML NVARCHAR(MAX)

SET @XML = N'
<root>
<marketDataSeries xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://pricesandcurves.ist.bp.com/xsd/CSL/IST/marketData/v1" xsi:schemaLocation="http://pricesandcurves.ist.bp.com/xsd/CSL/IST/marketData/v1 marketData.xsd" seriesID="urn:pc:FO.E.34639">
<observationDate>2013-07-15T00:00:00</observationDate>
<timeZone>London +0 (+1)</timeZone>
<observationPeriod>
<createDateTime>2013-07-15T14:26:15Z</createDateTime>
<updateDateTime>2013-07-15T14:26:15Z</updateDateTime>
<deliveryPeriod id="urn:pc:35F461C251D56382E04400144F22F0C2">
<relativePeriodLabel>SPOT</relativePeriodLabel>
</deliveryPeriod>
<obsType>
<name>HIGH</name>
<obs>
<status>Normal</status>
<value>623.5</value>
<obsExtension>
<actorId>FAME</actorId>
</obsExtension>
</obs>
</obsType>
<obsType>
<name>LOW</name>
<obs>
<status>Normal</status>
<value>622.5</value>
<obsExtension>
<actorId>FAME</actorId>
</obsExtension>
</obs>
</obsType>
<obsType>
<name>MID</name>
<obs>
<status>Normal</status>
<value>623</value>
<obsExtension>
<actorId>FAME</actorId>
</obsExtension>
</obs>
</obsType>
</observationPeriod>
</marketDataSeries>
</root>'

EXEC dbo.ParseXML_ByXMLInput
@XML
,'marketDataSeries'
,'/root'
,1
,0



CREATE PROCEDURE [dbo].[ParseXML_ByXMLInput]

@strXML NVARCHAR(MAX)
,@schemanode NVARCHAR(255)
,@rootnode NVARCHAR(255)
,@showAll BIT = 0
,@debug BIT = 0

AS
BEGIN

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
,@isSoap BIT = 0
,@checkSchema INT
,@isSchema BIT = 0
,@checkXMLHeader INT
,@isHeader BIT = 0
,@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 and extra spaces
SELECT @strXML = dbo.svfRemoveExcessSpacesFromXML(@strXML)

IF CHARINDEX('<',@strXML,1) > 0
BEGIN

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

IF @debug = 1
SELECT @strXML AS ReplacedXML
END
END


IF @debug = 1
SELECT @strXML AS InputXML


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

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

IF @checkXMLHeader > 0
SET @isHeader = 1


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

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

IF @debug = 1
SELECT @XMLHeader AS XMLHeader


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

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

IF @checkSoap > 0
SET @isSoap = 1


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

IF @isSoap = 1
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


IF @debug = 1
BEGIN
SELECT @SoapEnvOpen AS Soap_Wrapper
SELECT @SoapBodyOpen AS Soap_Body
SELECT @strXML AS XML_Without_SoapWrapper
END

--for testing
--SELECT @strXML
--RETURN


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

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

IF @checkSchema > 0
SET @isSchema = 1


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

IF @isSchema = 1
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

IF @debug = 1
BEGIN
SELECT @strXML AS ModifiedXML
SELECT CONVERT(XML,@strXML) AS FormattedXML
END

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


;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

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


--for testing
--SELECT * FROM #NodeList
--RETURN


;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
WHERE
Result.id >= 0
AND (@ShowAll = 0
AND (Result.id = 0
OR property IS NOT NULL
OR value IS NOT NULL
OR nodecontents IS NOT NULL))
OR (@ShowAll = 1)

END



 
Post #1474370
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse