I need to read the xml which is passed as an variable in stored procedure ?how to achieve that ?
-
I need to read the xml which is passed as an variable ,and insert its values to temporary table
@msgchunk ,after that insert @msgchunk table data to two tables .
I use this but Xml values where not inserted in to @msgchunk table :
DECLARE @idoc int, @doc varchar(1000);
SET @doc ='
<Survey>
<SResult SectionId="1" SectionName="Ownership" QuestionId="9" UserID="2" Answer="1" Status="checked" />
<SResult SectionId="1" SectionName="Ownership" QuestionId="8" UserID="2" Answer="null" Status="null" />
<SResult SectionId="1" SectionName="Ownership" QuestionId="6" UserID="2" Answer="null" Status="null" />
</Survey>
';
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;
------------- Creating virtual table for storing xml values
DECLARE @msgChunks TABLE(
SectionId int,
QuestionId nvarchar(50),
UserID int,
Answer nvarchar(50),
SectionName nvarchar(50),
SStatus nvarchar(50)
)
INSERT INTO @msgChunks(SectionId,SectionName,QuestionId,UserID,Answer,SStatus)
-- SELECT stmt using OPENXML rowset provider
SELECT CSectionId,CSectionName,CQuestionId,CUserID,CAnswer,CSStatus FROM OPENXML
(@idoc, '/Survey/SResult',2)
WITH
(
CSectionId int 'SectionId',
CSectionName nvarchar(10) 'SectionName',
CQuestionId nvarchar(50) 'QuestionId',
CUserID int 'UserID',
CAnswer nvarchar(10) 'Answer',
CSStatus nvarchar(10) 'Status'
);
select * from @msgChunks
For know i am tring to use xml as variable with xml. What to do ?
-
Assuming you are using SQL Server 2005+ you can simplify this with the xml data type.
DECLARE @doc xml;SET @doc ='
<Survey>
<SResult SectionId="1" SectionName="Ownership" QuestionId="9" UserID="2" Answer="1" Status="checked" />
<SResult SectionId="1" SectionName="Ownership" QuestionId="8" UserID="2" Answer="null" Status="null" />
<SResult SectionId="1" SectionName="Ownership" QuestionId="6" UserID="2" Answer="null" Status="null" />
</Survey>
';
SELECT
tbl.sr.value('@SectionId', 'int'),
tbl.sr.value('@SectionName', 'nvarchar(10)'),
tbl.sr.value('@QuestionId', 'nvarchar(50)'),
tbl.sr.value('@UserID', 'int'),
tbl.sr.value('@Answer', 'nvarchar(10)'),
tbl.sr.value('@Status', 'nvarchar(10)')
FROM @doc.nodes('Survey/SResult') AS tbl(sr)
A couple of observations/questions:
1. You have specified nvarchar(50) for QuestionId - shouldn't it be int?
2. Setting an attribute to null causes a varchar containing the string "null" to be returned. If you want the answer to be NULL, just remove that atribute from the xml.
-
I will try your code and did this,which resolve my problem and SP is working fine :
ALTER PROCEDURE [dbo].[USP_Insert_UserSurveyUsingXML]
-- Add the parameters for the stored procedure here
--@XmlHandle int,
@Xmlvalue as xml
AS
DECLARE
@XmlHandle int;
SET @XmlHandle =1;
--<Survey>
--<SResult SectionId="1" SectionName="Ownership" QuestionId="9" UserID="2" Answer="1" Status="checked" sDateTime="1/31/2013 10:16:53 AM" />
--<SResult SectionId="1" SectionName="Ownership" QuestionId="8" UserID="2" Answer="null" Status="null" sDateTime="1/31/2013 10:16:53 AM" />
--<SResult SectionId="1" SectionName="Ownership" QuestionId="6" UserID="2" Answer="null" Status="null" sDateTime="1/31/2013 10:16:53 AM" />
--</Survey>
--';
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @XmlHandle OUTPUT, @Xmlvalue ;
BEGIN
------------- Creating virtual table for storing splited values
DECLARE @XmlTb TABLE(
SectionId nvarchar(50),
QuestionId nvarchar(50),
UserID nvarchar(50),
Answer nvarchar(50),
SectionName nvarchar(50),
SStatus nvarchar(50),
sDateTime datetime
)
INSERT INTO @XmlTb(SectionId,SectionName,QuestionId,UserID,Answer,SStatus,sDateTime)
-- SELECT stmt using OPENXML rowset provider
SELECT *
FROM OPENXML (@XmlHandle, '/Survey/SResult',2)
WITH (SectionId nvarchar(50) '@SectionId',
SectionName varchar(10) '@SectionName',
QuestionId nvarchar(50) '@QuestionId',
UserID nvarchar(50) '@UserID',
Answer nvarchar(50) '@Answer',
tatur nvarchar(50) '@tatus',
sDateTime datetime '@sDateTime'
);
--Insert data into SurveyResultTb using Temporary table
Insert INTO SurveyResultTb(FKuserId,sDateTime)
select UserID,sDateTime from @XmlTb
--Insert data into answerTb using Temporary table
Insert INTO answerTb (FKquestionId,Answer)
select QuestionId,Answer from @XmlTb
What is the use of below line because when i didnt set to 1 then error comes:
SET @XmlHandle =1;
any suggesting regarding code ?
Thanks for replying
-
maida_rh (1/30/2013)
I will try your code and did this,which resolve my problem and SP is working fine :ALTER PROCEDURE [dbo].[USP_Insert_UserSurveyUsingXML]
-- Add the parameters for the stored procedure here
--@XmlHandle int,
@Xmlvalue as xml
AS
DECLARE
@XmlHandle int;
SET @XmlHandle =1;
--<Survey>
--<SResult SectionId="1" SectionName="Ownership" QuestionId="9" UserID="2" Answer="1" Status="checked" sDateTime="1/31/2013 10:16:53 AM" />
--<SResult SectionId="1" SectionName="Ownership" QuestionId="8" UserID="2" Answer="null" Status="null" sDateTime="1/31/2013 10:16:53 AM" />
--<SResult SectionId="1" SectionName="Ownership" QuestionId="6" UserID="2" Answer="null" Status="null" sDateTime="1/31/2013 10:16:53 AM" />
--</Survey>
--';
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @XmlHandle OUTPUT, @Xmlvalue ;
BEGIN
------------- Creating virtual table for storing splited values
DECLARE @XmlTb TABLE(
SectionId nvarchar(50),
QuestionId nvarchar(50),
UserID nvarchar(50),
Answer nvarchar(50),
SectionName nvarchar(50),
SStatus nvarchar(50),
sDateTime datetime
)
INSERT INTO @XmlTb(SectionId,SectionName,QuestionId,UserID,Answer,SStatus,sDateTime)
-- SELECT stmt using OPENXML rowset provider
SELECT *
FROM OPENXML (@XmlHandle, '/Survey/SResult',2)
WITH (SectionId nvarchar(50) '@SectionId',
SectionName varchar(10) '@SectionName',
QuestionId nvarchar(50) '@QuestionId',
UserID nvarchar(50) '@UserID',
Answer nvarchar(50) '@Answer',
tatur nvarchar(50) '@tatus',
sDateTime datetime '@sDateTime'
);
--Insert data into SurveyResultTb using Temporary table
Insert INTO SurveyResultTb(FKuserId,sDateTime)
select UserID,sDateTime from @XmlTb
--Insert data into answerTb using Temporary table
Insert INTO answerTb (FKquestionId,Answer)
select QuestionId,Answer from @XmlTb
What is the use of below line because when i didnt set to 1 then error comes:
SET @XmlHandle =1;
any suggesting regarding code ?
Thanks for replying
After calling sp_xml_preparedocument @XmlHandle will contain an integer which represents a pointer to the XML document as it is represented in memory. You do not need to initialize it to 1, i.e. this line is extraneous...
SET @XmlHandle =1;
...because when calling sp_xml_preparedocument the value stored in @XmlHandle will be set by the stored procedure. Try it, select @XmlHandle before and after the call to sp_xml_preparedocument. Try it several times in a row.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
I did't show any error while executing it as an query but when i create it as a stored procedure than it gives error, which is resolve by initializing it to 1 .
I don't have any idea why it is coming ?
-
You must have something else going on in the code on your side that you were not showing in your example. Here is your example with the SET statement commented out setup to return the data instead of insert it and it works just fine.
USE tempdbGO
CREATE PROCEDURE [dbo].[USP_Insert_UserSurveyUsingXML]
-- Add the parameters for the stored procedure here
--@XmlHandle int,
@Xmlvalue AS XML
AS
DECLARE @XmlHandle INT;
--SET @XmlHandle = 1;
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument
@XmlHandle OUTPUT,
@Xmlvalue;
BEGIN
------------- Creating virtual table for storing splited values
DECLARE @XmlTb TABLE
(
SectionId NVARCHAR(50),
QuestionId NVARCHAR(50),
UserID NVARCHAR(50),
Answer NVARCHAR(50),
SectionName NVARCHAR(50),
SStatus NVARCHAR(50),
sDateTime DATETIME
)
INSERT INTO @XmlTb
(
SectionId,
SectionName,
QuestionId,
UserID,
Answer,
SStatus,
sDateTime
)
-- SELECT stmt using OPENXML rowset provider
SELECT *
FROM OPENXML (@XmlHandle, '/Survey/SResult',2)
WITH (SectionId NVARCHAR(50) '@SectionId',
SectionName VARCHAR(10) '@SectionName',
QuestionId NVARCHAR(50) '@QuestionId',
UserID NVARCHAR(50) '@UserID',
Answer NVARCHAR(50) '@Answer',
tatur NVARCHAR(50) '@tatus',
sDateTime DATETIME '@sDateTime'
);
--Insert data into SurveyResultTb using Temporary table
SELECT UserID,
sDateTime
FROM @XmlTb
--Insert data into answerTb using Temporary table
SELECT QuestionId,
Answer
FROM @XmlTb
END
GO
EXEC dbo.USP_Insert_UserSurveyUsingXML
@Xmlvalue = '<Survey>
<SResult SectionId="1" SectionName="Ownership" QuestionId="9" UserID="2" Answer="1" Status="checked" sDateTime="1/31/2013 10:16:53 AM" />
<SResult SectionId="1" SectionName="Ownership" QuestionId="8" UserID="2" Answer="null" Status="null" sDateTime="1/31/2013 10:16:53 AM" />
<SResult SectionId="1" SectionName="Ownership" QuestionId="6" UserID="2" Answer="null" Status="null" sDateTime="1/31/2013 10:16:53 AM" />
</Survey>
';
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
You can also try shredding the entire XML into a table showing its parts. Then you have a better idea of the node structure or you can even build queries against the "XML" table.
This may not be as elegant as OPENXML or XQUERY, but it really helps me in that I can see the structure of an XML document in table form. This can be done just to help build XQUERY constructs or the table can be queried or joined just like any other table. The procedure and supporting functions are shown below..
See: http://www.sqlservercentral.com/Forums/Topic1413344-391-1.aspx#bm1414469
Example usage:
EXEC dbo.ParseXML_ByXMLInput
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.w3.org/2001/XMLSchema-instance">
<ReportPackage type="report">
<ReferenceId>XYZ-1234</ReferenceId>
<PackageInformation>
<References />
<Responses>
<Response name="UserId">17677</Response>
<Response name="UserGUID">de69c3c4-ed74-430c-8357-1baf3339ba8a</Response>
<Response name="TransactionToken">808f99ec-a39f-4666-be22-e36fbf3fc9b7</Response>
<Response name="Code">BA</Response>
<Response name="MembershipType">Premium</Response>
</Responses>
</PackageInformation>
<PersonalData>
<PersonName type="subject">
<GivenName>John</GivenName>
<MiddleName>Quincy</MiddleName>
<FamilyName>Adams</FamilyName>
<Suffix />
</PersonName>
<DemographicDetail>
<GovernmentId issuingAuthority="SSN">111-11-1111</GovernmentId>
<DateOfBirth>02/01/1975</DateOfBirth>
<Gender>M</Gender>
</DemographicDetail>
</PersonalData>
</ReportPackage>
</Reports>
</soapenv:Body>
</soapenv:Envelope>'
,'Reports'
,'/ReportPackage'
,1
,0
/*
Parameters
@strXML NVARCHAR(MAX)
,@schemanode NVARCHAR(255)
,@rootnode NVARCHAR(255)
,@showAll BIT = 0
,@debug BIT = 0
Returns
RowNum
,id
,parentid
,nodepath
,nodetype
,nodename
,property
,value
,nodecontents
,XMLStatus
*/
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
/* 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
;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
GO
CREATE FUNCTION [dbo].[svfRemoveExcessSpaces]
(
@strText NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
BEGIN
DECLARE
@STR NVARCHAR(MAX)
,@xml XML
SET @STR = @strText
SET @STR = REPLACE(@str,CHAR(9),' ') -- convert tabs to spaces
SET @STR =
REPLACE(
REPLACE(
REPLACE(
,' ',' '+CHAR(7))
,CHAR(7)+' ','')
,CHAR(7),'')
RETURN @STR
END
GO
CREATE FUNCTION [dbo].[tvfDelimitedSplit]
(
@pString VARCHAR(8000)
,@pDelimiter CHAR(1)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
WITH
cteTally(N)
AS
(
SELECT TOP (ISNULL(DATALENGTH(@pString),0))
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM
dbo.Tally
)
,cteStart(N1)
AS
(
SELECT
1
UNION ALL
SELECT
t.N + 1
FROM
cteTally t
WHERE
SUBSTRING(@pString,t.N,1) = @pDelimiter
)
,cteLen(N1,L1)
AS
(
SELECT
s.N1
,ISNULL(
NULLIF(
CHARINDEX(@pDelimiter,@pString,s.N1),0)
- s.N1,8000)
FROM
cteStart s
)
SELECT
ItemNumber = ROW_NUMBER() OVER (ORDER BY l.N1)
,Item = SUBSTRING(@pString,l.N1,l.L1)
FROM
cteLen l;
GO
Here's the output for your XML input string:
RowNumidparentidnodepathnodetypenodenamepropertyvaluenodecontentsXMLStatus
10NULL/SurveyRootSurveyNULLNULLNULLOK
220/Survey/SResultNodeSResultNULLNULLNULLOK
332/Survey/SResult/SectionIdPropertySResultSectionId1NULLOK
442/Survey/SResult/SectionNamePropertySResultSectionNameOwnershipNULLOK
552/Survey/SResult/QuestionIdPropertySResultQuestionId9NULLOK
662/Survey/SResult/UserIDPropertySResultUserID2NULLOK
772/Survey/SResult/AnswerPropertySResultAnswer1NULLOK
882/Survey/SResult/StatusPropertySResultStatuscheckedNULLOK
990/Survey/SResultNodeSResultNULLNULLNULLOK
10109/Survey/SResult/SectionIdPropertySResultSectionId1NULLOK
11119/Survey/SResult/SectionNamePropertySResultSectionNameOwnershipNULLOK
12129/Survey/SResult/QuestionIdPropertySResultQuestionId8NULLOK
13139/Survey/SResult/UserIDPropertySResultUserID2NULLOK
14149/Survey/SResult/AnswerPropertySResultAnswernullNULLOK
15159/Survey/SResult/StatusPropertySResultStatusnullNULLOK
16160/Survey/SResultNodeSResultNULLNULLNULLOK
171716/Survey/SResult/SectionIdPropertySResultSectionId1NULLOK
181816/Survey/SResult/SectionNamePropertySResultSectionNameOwnershipNULLOK
191916/Survey/SResult/QuestionIdPropertySResultQuestionId6NULLOK
202016/Survey/SResult/UserIDPropertySResultUserID2NULLOK
212116/Survey/SResult/AnswerPropertySResultAnswernullNULLOK
222216/Survey/SResult/StatusPropertySResultStatusnullNULLOK
-
I have to ask. If you're using SQL Server 2008, why do you insist on using OPENXML?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Complete Stored procedure code :
ALTER PROCEDURE [dbo].[USP_Insert_UserSurveyUsingXML]--@XmlHandle int,
@Xmlvalue as xml
AS
DECLARE
@XmlHandle int;
SET @XmlHandle =1;
--<Survey>
--<SResult SectionId="1" SectionName="Ownership" QuestionId="9" UserID="2" Answer="1" Status="checked" sDateTime="1/31/2013 10:16:53 AM" />
--<SResult SectionId="1" SectionName="Ownership" QuestionId="8" UserID="2" Answer="null" Status="null" sDateTime="1/31/2013 10:16:53 AM" />
--<SResult SectionId="1" SectionName="Ownership" QuestionId="6" UserID="2" Answer="null" Status="null" sDateTime="1/31/2013 10:16:53 AM" />
--</Survey>
--';
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @XmlHandle OUTPUT, @Xmlvalue ;
BEGIN
-------------------------------CREATU+ING VIRTUAL TABLE ---------------------------------------
DECLARE @XmlTb TABLE(
ID int NOT NULL IDENTITY (1,1) PRIMARY KEY,
SectionId nvarchar(50),
QuestionId nvarchar(50),
UserID nvarchar(50),
Answer nvarchar(50),
SectionName nvarchar(50),
SStatus nvarchar(50),
sDateTime datetime,
sResultID int
)
--------------------------------INSERTION IN TEMPORARY TABLE-----------------------------------
INSERT INTO @XmlTb(SectionId,SectionName,QuestionId,UserID,Answer,SStatus,sDateTime,sResultID)
------------------------SELECT stmt using OPENXML rowset provider------------------------------
SELECT *
FROM OPENXML (@XmlHandle, '/Survey/SResult',2)
WITH (SectionId nvarchar(50) '@SectionId',
SectionName varchar(10) '@SectionName',
QuestionId nvarchar(50) '@QuestionId',
UserID nvarchar(50) '@UserID',
Answer nvarchar(50) '@Answer',
tatur nvarchar(50) '@tatus',
sDateTime datetime '@sDateTime',
sResultID int 'null'
);
-----------------------------SELECT UserID to Store Survey Details-----------------------------------
Declare @userid int,@resultd int;
SET @userid=(Select TOP 1(UserID) from @XmlTb )
-----------------------------UPDATE user IsMember status in userTb-----------------------------------
Update userTb SET IsMember ='True' where UserId =@userId
--------------------Insert data into SurveyResultTb using Temporary table----------------------------
Insert INTO SurveyResultTb(FKuserId,sDateTime)
SELECT DISTINCT UserID, sDateTime FROM @xmlTB WHERE UserID = @userid
-----------------------------SELECT sResultID to UPDATE @xmlTB---------------------------------------
SET @resultd=(select top 1(SResultID) from SurveyResultTb where FKuserId =@userId ORDER BY SResultID DESC)
Update @xmlTB SET sResultID =@resultd where UserID =@userId
------------------------Insert data into answerTb using Temporary table------------------------
Insert INTO answerTb (FKquestionId,Answer,FKsurveyResultID)
select QuestionId,Answer,sResultID from @XmlTb where UserID =@userId
END
The XML that is returned by asp.net (C#) is passed as a parameter in stored procedure and commented for reference .Kindly let me know
-
I used this url for insertion using XML:
-
maida_rh (2/3/2013)
I used this url for insertion using XML:Ah! I see.
There are some myths and truths about performance and memory usage concerning OPENXML and the alternative "Nodes" methods. A decent discussion on both can be found at the following URL.
http://social.msdn.microsoft.com/Forums/eu/sqlxml/thread/e51ef84d-72d6-490a-844a-fe28fbea3648
As with everything else in SQL, "It Depends". The posts at the URL above seem to indicate that the "Nodes" method is faster and less memory intensive than OPENXML for smaller things unless you have the correct XML indexes. Without the idexes, OPENXML is faster for the larger stuff. Some claim that the memory usage is horrible and others don't seem to have a problem with it. I thought the URL I posted would be a good start to make you aware of some of the reported differences so that you kow what you're dealing with.
The Google search I used to find that URL and much more is "sql openxml memory usage".
Of course, if I had my druthers, XML would simply not exist. It's horribly bloated with unnecessary information even in its simplest forms. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks for sharing the URL ,as ,I clear my questions regarding OPENML and in my senario I preferred to use OPENXML instead of node().
Some url suggested to use :
EXEC sp_xml_removedocument @hdocafter OPENXML,Can anyone specify where the above line of code is used regarding my posted code ?
Thanks lot
-
Anywhere in your code once you're done using the document.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
opc.three (2/6/2013)
Anywhere in your code once you're done using the document.Agreed with a qualifier... just as soon as you don't need the document to be in an open state anymore. Close it as soon as you can in the code. Even Books Online for 2012 says that a single instance of OPENXML takes 1/8 of the memory available to SQL Server.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I am not sure I understand the qualifier you mentioned. How is "done using" different from "don't need the document to be in an open state"?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply