some one please help me to read this xml-data from a XML variable

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » some one please help me to read this xml-data...

some one please help me to read this xml-data from a XML variable

Rate Topic

Display Mode

Topic Options

Author

Message

prabhu.st

Posted Saturday, February 02, 2013 12:04 PM

Grasshopper

Group: General Forum Members
Last Login: Sunday, May 19, 2013 2:51 AM
Points: 10, Visits: 33

HI,

I have a @xml variable, I need to read this variable and to insert the values into my table in SQL 2008 R2 DB.

I have tried in somay ways, but nothing worked for me since I have not practised using xqueries or XML datatypes, I am struggling a lot, I have attached the sample xml and how it gets populated as an XML variable everything has been given with the attachment, please have a look and help me to get out of this.

Any / Every help would be much appreciated.

Thanks,
Prabhu

Post Attachments

blog_doubt.txt (12 views, 78.58 KB)

Post #1414958

prabhu.st

Posted Monday, February 04, 2013 10:58 AM

Grasshopper

Group: General Forum Members
Last Login: Sunday, May 19, 2013 2:51 AM
Points: 10, Visits: 33

I really worried that there is nobody to help me regarding this issue, I was so confident that I could get a solution from this forum, but so far I haven't got anything. I don't where I mistook.

please somebody suggest me atleast the mistake in my blog. so that I could give you a clear view on my Question/Doubt I have aske you guys.

My appologize for my "ENGLISH" If it hurts you in any case..

Thanks,
Prabhu

Post #1415409

dwain.c

Posted Monday, February 04, 2013 6:06 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:24 PM
Points: 2,346, Visits: 3,192

I get these errors when I run your SQL.

(780 row(s) affected)
Msg 207, Level 16, State 1, Line 792
Invalid column name 'questionnbr'.
Msg 207, Level 16, State 1, Line 792
Invalid column name 'questionnbr'.
Msg 207, Level 16, State 1, Line 792
Invalid column name 'qvalue'.

Can't help if I can't get it to run.

No loops! No CURSORs! No RBAR! Hoo-uh!

INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!

Post #1415547

Steven Willis

Posted Tuesday, February 05, 2013 12:28 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 10:03 PM
Points: 284, Visits: 1,248

The first problem was that your column names in #loanheader didn't match the column names in the query. Search and replace fixed that and I was able to get valid XML output.

Now your second problem is more difficult. Fortunately, I have a stored procedure that will parse your XML and put it into a table for you. The code for the procedure and the code that runs it is below.

Be patient...your XML file is VERY large and it will take a long time to run--on my laptop it took over 4 minutes. I have it going to a #TempTable, but you can change that to a permanent table of course. I had to break your XML into "chunks" because the parse procedure uses a recursive CTE and after so many rows it just seemed to take forever. But once you have the data in a table you can do whatever you need to do.

If you are going to run this frequently enough that performance is an issue, you can chop out the recursive CTE. The CTE just takes data from two smaller temp tables and organizes it a little better for presentation purposes. I've always dealt with XML files of less than a few hundred lines and maybe a dozen nodes so it hasn't been a problem for me. But you could easily modify the function to put the data directly into a table without going through the recursive routine. Without the CTE the whole process should take less than 10 seconds!


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

CREATE TABLE #loanheader (
	[ID] INT IDENTITY(1,1) NOT NULL,
	[-1]  NVARCHAR(510) NULL,
	[-2]  NVARCHAR(510) NULL,
	[-3]  NVARCHAR(510) NULL,
	[-4]  NVARCHAR(510) NULL,
	[-5]  NVARCHAR(510) NULL,
	[-6]  NVARCHAR(510) NULL,
	[-7]  NVARCHAR(510) NULL,
	[-8]  NVARCHAR(510) NULL,
	[-9]  NVARCHAR(510) NULL,
	[-10]  NVARCHAR(510) NULL,
	[-11]  NVARCHAR(510) NULL,
	[-12]  NVARCHAR(510) NULL,
	[-13]  NVARCHAR(510) NULL,
	[-14]  NVARCHAR(510) NULL,
	[-15]  NVARCHAR(510) NULL,
	PRIMARY KEY (ID))

/* Assigning values for Loan Header */

INSERT INTO #loanheader
SELECT 
	*
FROM
    (
     SELECT QuestionID, Quest_Value FROM dbo.LoanDetail WHERE QuestionID < 0 
    ) p PIVOT
		( MAX(Quest_Value) FOR QuestionID IN ([-1],[-2],[-3],[-4],[-5],[-6],[-7],[-8],[-9],[-10],[-11],[-12],[-13],[-14],[-15]) )as pvt;

	
DECLARE 
	 @systemxml XML 
	,@q1XML XML	
	,@q2XML XML	
	,@q3XML XML	
	,@q4XML XML
	,@userxml XML 	
	,@buyerxml XML 
	,@calcxml XML 	

SET @systemxml = (
                  SELECT
                    '1.0' AS '@Version'
                   ,'-1' AS '@CaseName'
                   ,'-2' AS '@CaseProgram'
                   ,'-3' AS '@CaseInsured'
                   ,'-4' AS '@CaseIns'
                   ,'-5' AS '@CaseState'
                   ,'-6' AS '@CaseRefi'
                   ,'-7' AS '@CaseBydn'
                   ,'-8' AS '@CaseBlln'
                   ,'-9' AS '@CaseSecMtg'
                   ,'-10' AS '@ActiveBuyer'
                   ,'-11' AS '@Mode'
                   ,'-12' AS '@Severe'
                   ,'-13' AS '@Warning'
                   ,'-14' AS '@WholesaleIndicator'
                   ,'-15' AS '@HELOC'
                  FROM
                    #loanheader
                 FOR
                  XML PATH('System')
                     ,TYPE
                  )
                                 
  				/* System Loans */
  				SET @q1XML = 
  					(SELECT
  						(SELECT
                            CAST(QuestionID AS VARCHAR(MAX)) AS [@Num]
                           ,(CASE WHEN COUNT(ElementID) > 1 THEN 'Y'
                             END) AS [@Multivalue]
                           ,(CASE WHEN COUNT(ElementID) > 1 THEN CAST(MAX(CAST(ElementID AS VARCHAR(MAX))) AS XML)
                                  ELSE MAX(Quest_Value)
                             END)
                         FROM
                            (
                             SELECT
                                QuestionID
                               ,(
                                 SELECT
                                    ElementID AS [@Num]
                                   ,MAX(Quest_Value)
                                 FROM
                                    dbo.LoanDetail ld
                                 WHERE
                                    Loan_ID > 0
                                    AND loan_id = ld1.loan_id
                                    AND ld.QuestionID = ld1.QuestionID
                                 GROUP BY
                                    ld.QuestionID
                                   ,ld.ElementID
                                   ,ld.Quest_Value
                                FOR
                                 XML PATH('Offset')
                                    ,TYPE
                                ) ElementID
                               ,Quest_Value
                             FROM
                                dbo.LoanDetail ld1
                            ) a
                         WHERE
                            (
                            a.QuestionID > 0
                            AND a.QuestionID <= 500
                            )
                         GROUP BY
                            a.QuestionID
                        FOR
                         XML PATH('Question')
                            ,TYPE
                        )
                  FROM
                    #loanheader
                 FOR
                  XML PATH('System')
                     ,TYPE
                 )                       

  				SET @q2XML = 
  					(SELECT
  						(SELECT
                            CAST(QuestionID AS VARCHAR(MAX)) AS [@Num]
                           ,(CASE WHEN COUNT(ElementID) > 1 THEN 'Y'
                             END) AS [@Multivalue]
                           ,(CASE WHEN COUNT(ElementID) > 1 THEN CAST(MAX(CAST(ElementID AS VARCHAR(MAX))) AS XML)
                                  ELSE MAX(Quest_Value)
                             END)
                         FROM
                            (
                             SELECT
                                QuestionID
                               ,(
                                 SELECT
                                    ElementID AS [@Num]
                                   ,MAX(Quest_Value)
                                 FROM
                                    dbo.LoanDetail ld
                                 WHERE
                                    Loan_ID > 0
                                    AND loan_id = ld1.loan_id
                                    AND ld.QuestionID = ld1.QuestionID
                                 GROUP BY
                                    ld.QuestionID
                                   ,ld.ElementID
                                   ,ld.Quest_Value
                                FOR
                                 XML PATH('Offset')
                                    ,TYPE
                                ) ElementID
                               ,Quest_Value
                             FROM
                                dbo.LoanDetail ld1
                            ) a
                         WHERE
                            (
                            a.QuestionID > 500
                            AND a.QuestionID <= 650
                            )
                         GROUP BY
                            a.QuestionID
                        FOR
                         XML PATH('Question')
                            ,TYPE
                        )
                  FROM
                    #loanheader
                 FOR
                  XML PATH('System')
                     ,TYPE
                 ) 

  				SET @q3XML = 
  					(SELECT
  						(SELECT
                            CAST(QuestionID AS VARCHAR(MAX)) AS [@Num]
                           ,(CASE WHEN COUNT(ElementID) > 1 THEN 'Y'
                             END) AS [@Multivalue]
                           ,(CASE WHEN COUNT(ElementID) > 1 THEN CAST(MAX(CAST(ElementID AS VARCHAR(MAX))) AS XML)
                                  ELSE MAX(Quest_Value)
                             END)
                         FROM
                            (
                             SELECT
                                QuestionID
                               ,(
                                 SELECT
                                    ElementID AS [@Num]
                                   ,MAX(Quest_Value)
                                 FROM
                                    dbo.LoanDetail ld
                                 WHERE
                                    Loan_ID > 0
                                    AND loan_id = ld1.loan_id
                                    AND ld.QuestionID = ld1.QuestionID
                                 GROUP BY
                                    ld.QuestionID
                                   ,ld.ElementID
                                   ,ld.Quest_Value
                                FOR
                                 XML PATH('Offset')
                                    ,TYPE
                                ) ElementID
                               ,Quest_Value
                             FROM
                                dbo.LoanDetail ld1
                            ) a
                         WHERE
                            (
                            a.QuestionID >= 650
                            AND a.QuestionID <= 675
                            )
                         GROUP BY
                            a.QuestionID
                        FOR
                         XML PATH('Question')
                            ,TYPE
                        )
                  FROM
                    #loanheader
                 FOR
                  XML PATH('System')
                     ,TYPE
                 ) 

  				SET @q4XML = 
  					(SELECT
  						(SELECT
                            CAST(QuestionID AS VARCHAR(MAX)) AS [@Num]
                           ,(CASE WHEN COUNT(ElementID) > 1 THEN 'Y'
                             END) AS [@Multivalue]
                           ,(CASE WHEN COUNT(ElementID) > 1 THEN CAST(MAX(CAST(ElementID AS VARCHAR(MAX))) AS XML)
                                  ELSE MAX(Quest_Value)
                             END)
                         FROM
                            (
                             SELECT
                                QuestionID
                               ,(
                                 SELECT
                                    ElementID AS [@Num]
                                   ,MAX(Quest_Value)
                                 FROM
                                    dbo.LoanDetail ld
                                 WHERE
                                    Loan_ID > 0
                                    AND loan_id = ld1.loan_id
                                    AND ld.QuestionID = ld1.QuestionID
                                 GROUP BY
                                    ld.QuestionID
                                   ,ld.ElementID
                                   ,ld.Quest_Value
                                FOR
                                 XML PATH('Offset')
                                    ,TYPE
                                ) ElementID
                               ,Quest_Value
                             FROM
                                dbo.LoanDetail ld1
                            ) a
                         WHERE
                            (
                            a.QuestionID >= 675
                            AND a.QuestionID <= 1000
                            )
                            OR (a.QuestionID > 20000)
                         GROUP BY
                            a.QuestionID
                        FOR
                         XML PATH('Question')
                            ,TYPE
                        )
                  FROM
                    #loanheader
                 FOR
                  XML PATH('System')
                     ,TYPE
                 ) 
	
	/* User Loans */
		SET @userxml = 
                   (SELECT
                        (
                         SELECT
                        CAST(QuestionID AS VARCHAR(MAX)) AS [@Num]
                       ,(CASE WHEN COUNT(ElementID) > 1 THEN 'Y'
                         END) AS [@Multivalue]
                       ,(CASE WHEN COUNT(ElementID) > 1 THEN CAST(MAX(CAST(ElementID AS VARCHAR(MAX))) AS XML)
                              ELSE MAX(Quest_Value)
                         END)
                     FROM
                        (
                         SELECT
                            QuestionID
                           ,(
                             SELECT
                                ElementID AS [@Num]
                               ,MAX(Quest_Value)
                             FROM
                                dbo.LoanDetail ld
                             WHERE
                                Loan_ID > 0
                                AND loan_id = ld1.loan_id
                                AND ld.QuestionID = ld1.QuestionID
                             GROUP BY
                                ld.QuestionID
                               ,ld.ElementID
                               ,ld.Quest_Value
                            FOR
                             XML PATH('Offset')
                                ,TYPE
                            ) ElementID
                           ,Quest_Value
                         FROM
                            dbo.LoanDetail ld1
                        ) a
                     WHERE
                        a.QuestionID > 10000
                        AND a.QuestionID < 11999
                     GROUP BY
                        a.QuestionID
                       FOR
                         XML PATH('Question')
                            ,TYPE
                        )
                  FROM
                    #loanheader
                 FOR
                  XML PATH('LoanData')
                     ,TYPE
                 )   
	
	/*Buyer Data*/
	SET @buyerxml = 
				(SELECT
                    '1' AS [@Num]
                      ,(SELECT                        
                           CAST((QuestionID - 1000) AS VARCHAR(MAX)) AS [@Num]
                           ,(CASE WHEN COUNT(ElementID) > 1 THEN 'Y'
                             END) AS [@Multivalue]
                           ,(CASE WHEN COUNT(ElementID) > 1 THEN CAST(MAX(CAST(ElementID AS VARCHAR(MAX))) AS XML)
                                  ELSE MAX(Quest_Value)
                             END)
                         FROM
                            (
                             SELECT
                                QuestionID
                               ,(
                                 SELECT
                                    ElementID AS [@Num]
                                   ,MAX(Quest_Value)
                                 FROM
                                    dbo.LoanDetail ld
                                 WHERE
                                    Loan_ID > 0
                                    AND loan_id = ld1.loan_id
                                    AND ld.QuestionID = ld1.QuestionID
                                 GROUP BY
                                    ld.QuestionID
                                   ,ld.ElementID
                                   ,ld.Quest_Value
                                FOR
                                 XML PATH('Offset')
                                    ,TYPE
                                ) ElementID
                               ,Quest_Value
                             FROM
                                dbo.LoanDetail ld1
                            ) a
                         WHERE
                            a.QuestionID > 1000
                            AND a.QuestionID < 1999
                         GROUP BY
                            a.QuestionID
                        FOR
                         XML PATH('Question')
                            ,TYPE
                        )
                  FROM
                    #loanheader
                 FOR
                  XML PATH('BuyerData')
                     ,TYPE
                 )   
	
	/* Calc data */
	SET @calcxml = 
                   (SELECT
                        (
                         SELECT
                            CAST(QuestionID AS VARCHAR(MAX)) AS [@Num]
                           ,(CASE WHEN COUNT(ElementID) > 1 THEN 'Y'
                             END) AS [@Multivalue]
                           ,(CASE WHEN COUNT(ElementID) > 1 THEN CAST(MAX(CAST(ElementID AS VARCHAR(MAX))) AS XML)
                                  ELSE MAX(Quest_Value)
                             END)
                         FROM
                            (
                             SELECT
                                QuestionID
                               ,(
                                 SELECT
                                    ElementID AS [@Num]
                                   ,MAX(Quest_Value)
                                 FROM
                                    dbo.LoanDetail ld
                                 WHERE
                                    Loan_ID > 0
                                    AND loan_id = ld1.loan_id
                                    AND ld.QuestionID = ld1.QuestionID
                                 GROUP BY
                                    ld.QuestionID
                                   ,ld.ElementID
                                   ,ld.Quest_Value
                                FOR
                                 XML PATH('Offset')
                                    ,TYPE
                                ) ElementID
                               ,Quest_Value
                             FROM
                                dbo.LoanDetail ld1
                            ) a
                         WHERE
                            a.QuestionID > 7000
                            AND a.QuestionID < 8000
                         GROUP BY
                            a.QuestionID
                        FOR
                         XML PATH('Question')
                            ,TYPE
                        )
                  FROM
                    #loanheader
                 FOR
                  XML PATH('CalcData')
                     ,TYPE
                 )
	

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

CREATE TABLE #TempTable (
	[RowNum] INT IDENTITY(1,1) NOT NULL,
	[groupitem] INT NULL,
	[nodeid] INT NULL,
	[parentid] INT NULL,
	[nodetype] NVARCHAR(50) NULL,
	[nodename] NVARCHAR(50) NULL,
	[property] NVARCHAR(50) NULL,
	[value] NVARCHAR(50) NULL,
	[nodecontents] NVARCHAR(50) NULL,
	PRIMARY KEY (RowNum))

DECLARE 
	 @strXML NVARCHAR(MAX)
    ,@TempXML XML


SET @strXML = CONVERT(NVARCHAR(MAX),@systemxml)
INSERT INTO #TempTable
EXEC dbo.uspParseXML @strXML, N'System'

SET @strXML = CONVERT(NVARCHAR(MAX),@q1XML)
INSERT INTO #TempTable
EXEC dbo.uspParseXML @strXML, N'System'

SET @strXML = CONVERT(NVARCHAR(MAX),@q2XML)
INSERT INTO #TempTable
EXEC dbo.uspParseXML @strXML, N'System'

SET @strXML = CONVERT(NVARCHAR(MAX),@q3XML)
INSERT INTO #TempTable
EXEC dbo.uspParseXML @strXML, N'System'

SET @strXML = CONVERT(NVARCHAR(MAX),@q4XML)
INSERT INTO #TempTable
EXEC dbo.uspParseXML @strXML, N'System'

SET @strXML = CONVERT(NVARCHAR(MAX),@userxml)
INSERT INTO #TempTable
EXEC dbo.uspParseXML @strXML, N'LoanData'

SET @strXML = CONVERT(NVARCHAR(MAX),@buyerxml)
INSERT INTO #TempTable
EXEC dbo.uspParseXML @strXML, N'BuyerData'

SET @strXML = CONVERT(NVARCHAR(MAX),@calcxml)
INSERT INTO #TempTable
EXEC dbo.uspParseXML @strXML, N'CalcData'

SELECT
	* 
FROM 
	#TempTable AS tt


/* These skip the recursion */

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

CREATE TABLE #SimpleTable (
	[RowNum] INT NULL,
	[Id] INT NULL,
	[ParentId] INT NULL,
	[nodetype] NVARCHAR(50) NULL,
	[localname] NVARCHAR(50) NULL,
	[text] NVARCHAR(500) NULL)

SET @strXML = CONVERT(NVARCHAR(MAX),@systemxml)
INSERT INTO #SimpleTable
EXEC dbo.uspParseXML_NO_CTE @strXML, N'System'

SET @strXML = CONVERT(NVARCHAR(MAX),@q1XML)
INSERT INTO #SimpleTable
EXEC dbo.uspParseXML_NO_CTE @strXML, N'System'

SET @strXML = CONVERT(NVARCHAR(MAX),@q2XML)
INSERT INTO #SimpleTable
EXEC dbo.uspParseXML_NO_CTE @strXML, N'System'

SET @strXML = CONVERT(NVARCHAR(MAX),@q3XML)
INSERT INTO #SimpleTable
EXEC dbo.uspParseXML_NO_CTE @strXML, N'System'

SET @strXML = CONVERT(NVARCHAR(MAX),@q4XML)
INSERT INTO #SimpleTable
EXEC dbo.uspParseXML_NO_CTE @strXML, N'System'

SET @strXML = CONVERT(NVARCHAR(MAX),@userxml)
INSERT INTO #SimpleTable
EXEC dbo.uspParseXML_NO_CTE @strXML, N'LoanData'

SET @strXML = CONVERT(NVARCHAR(MAX),@buyerxml)
INSERT INTO #SimpleTable
EXEC dbo.uspParseXML_NO_CTE @strXML, N'BuyerData'

SET @strXML = CONVERT(NVARCHAR(MAX),@calcxml)
INSERT INTO #SimpleTable
EXEC dbo.uspParseXML_NO_CTE @strXML, N'CalcData'
	
	
SELECT
	* 
FROM 
	#SimpleTable AS tt

The procedure:



CREATE PROCEDURE [dbo].[uspParseXML]

     @strXML AS XML
    ,@rootnode NVARCHAR(255)

AS
BEGIN
    
    SET NOCOUNT ON

    DECLARE
        @strText AS NVARCHAR(MAX)
       ,@idoc INT
       ,@id INT
       ,@parentid INT

    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(4000) NULL,
        [text] NVARCHAR(4000) NULL,
        PRIMARY KEY (RowNum),
        UNIQUE (RowNum))

    SET @id = 1
    SET @parentid = NULL

	
    /* Get rid of tabs and extra spaces */

    SET @strText = CAST(@strXML AS NVARCHAR(MAX))

    SET @strText = 
    REPLACE(
        REPLACE(
            REPLACE(
                REPLACE(
                    @strText
                ,'  ',' '+CHAR(7))
            ,CHAR(7)+' ','')
        ,CHAR(7),'')
    ,CHAR(9),'    ')
        
    SET @strXML = CONVERT(XML,@strText)


    /* Validate the XML */
    
    EXEC sp_xml_preparedocument @idoc OUTPUT, @strXML
    
    
    /* Parse the XML data */
    
    ;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(4000)) AS [text]
    FROM #ChildList
    INNER JOIN 
        OPENXML (@idoc,@rootnode,2) AS xmllist
        ON #ChildList.id = xmllist.id  
    WHERE
        #ChildList.RowNum > 0
		
	
    /* Display the results */
                
    ;WITH RecursiveNodes(RowNum,id,parentid,localname,[text],nodetype)
    AS (
        SELECT
             #NodeList.RowNum
            ,#NodeList.id
            ,#NodeList.parentid
            ,#NodeList.localname
            ,CAST(#NodeList.[text] AS NVARCHAR(4000)) 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
            ,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.nodetype
       ,Result.nodename
       ,Result.property
       ,Result.value
	   ,Result.nodecontents
    FROM
        (
        SELECT
            rn.RowNum
           ,rn.id
           ,rn.parentid
           ,(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 rn
        WHERE
            rn.localname <> '#text'
        ) AS Result
    WHERE
        Result.id >= 0
        AND (Result.id = 0
            OR property IS NOT NULL
            OR value IS NOT NULL
            OR nodecontents IS NOT NULL)
    ORDER BY
		Result.id
    OPTION (MAXRECURSION 0)
    
            
END


CREATE PROCEDURE [dbo].[uspParseXML_NO_CTE]

     @strXML AS XML
    ,@rootnode NVARCHAR(255)

AS
BEGIN
    
    SET NOCOUNT ON

    DECLARE
        @strText AS NVARCHAR(MAX)
       ,@idoc INT
       ,@id INT
       ,@parentid INT

    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(4000) NULL,
        [text] NVARCHAR(4000) NULL,
        PRIMARY KEY (RowNum),
        UNIQUE (RowNum))

    SET @id = 1
    SET @parentid = NULL

	
    /* Get rid of tabs and extra spaces */

    SET @strText = CAST(@strXML AS NVARCHAR(MAX))

    SET @strText = 
    REPLACE(
        REPLACE(
            REPLACE(
                REPLACE(
                    @strText
                ,'  ',' '+CHAR(7))
            ,CHAR(7)+' ','')
        ,CHAR(7),'')
    ,CHAR(9),'    ')
        
    SET @strXML = CONVERT(XML,@strText)


    /* Validate the XML */
    
    EXEC sp_xml_preparedocument @idoc OUTPUT, @strXML
    
    
    /* Parse the XML data */
    
    ;WITH cte   --this cte is required
    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(4000)) AS [text]
    FROM #ChildList
    INNER JOIN 
        OPENXML (@idoc,@rootnode,2) AS xmllist
        ON #ChildList.id = xmllist.id  
    WHERE
        #ChildList.RowNum > 0
		
	
    SELECT
	    RowNum
	   ,id
	   ,parentid
	   ,nodetype
	   ,localname
	   ,[text]
	FROM
        #NodeList
        
           
END

I also added some indexes to the LoanDetail table



CREATE TABLE [dbo].[LoanDetail](
	[Loan_ID] [nchar](25) NOT NULL,
	[QuestionID] [int] NOT NULL,
	[ElementID] [smallint] NOT NULL,
	[Quest_Value] [nvarchar](510) NULL,
 CONSTRAINT [PK_LoanDetail] PRIMARY KEY CLUSTERED 
(
	[Loan_ID] ASC,
	[QuestionID] ASC,
	[ElementID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


CREATE NONCLUSTERED INDEX [IX_LoanDetail_QValue] ON [dbo].[LoanDetail] 
(
	[Loan_ID] ASC,
	[QuestionID] ASC,
	[ElementID] ASC
)
INCLUDE ( [Quest_Value]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Post #1415605

prabhu.st

Posted Tuesday, February 05, 2013 4:28 AM

Grasshopper

Group: General Forum Members
Last Login: Sunday, May 19, 2013 2:51 AM
Points: 10, Visits: 33

Hi Steven,

I am really glad to see your post, My Hearty thanks for all your time & effort to help, really your Idea have helped me to get out this issue faster.

the suggestions based on the XML data formation and the Procedure Creation sounds great, and already we had the Index both primary and non-clustered index on the base table "
loandetail"..

Thank you so much.
Prabhu

Post #1415725

prabhu.st

Posted Tuesday, February 05, 2013 4:30 AM

Grasshopper

Group: General Forum Members
Last Login: Sunday, May 19, 2013 2:51 AM
Points: 10, Visits: 33

Thanks for your effort dwain,

I think I have missed out something while populating the #loanheader, you my try the script with #loandetail instead of loandetail in the script directly. I am sorry about it.

Thanks,
Prabhu

Post #1415727

« Prev Topic | Next Topic »

Permissions