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 tempdb

    GO

    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(

    @STR

    ,' ',' '+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

     

  • @maida_rh,

    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:

    msdn help for OPENXML (Transact-SQL)

  • maida_rh (2/3/2013)


    I used this url for insertion using XML:

    msdn help for OPENXML (Transact-SQL)

    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 @hdoc

    after 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