InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 03:32:10 GMT20http://www.sqlservercentral.com/Forums/Topic1414958-338-1.aspxThanks 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,PrabhuTue, 05 Feb 2013 04:30:01 GMTprabhu.sthttp://www.sqlservercentral.com/Forums/Topic1414958-338-1.aspxHi 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.PrabhuTue, 05 Feb 2013 04:28:34 GMTprabhu.sthttp://www.sqlservercentral.com/Forums/Topic1414958-338-1.aspxThe 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! [code="sql"]IF OBJECT_ID('tempdb..#loanheader') IS NOT NULL DROP TABLE #loanheaderCREATE 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 #loanheaderSELECT *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 NULLDROP TABLE #TempTableCREATE 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 XMLSET @strXML = CONVERT(NVARCHAR(MAX),@systemxml)INSERT INTO #TempTableEXEC dbo.uspParseXML @strXML, N'System'SET @strXML = CONVERT(NVARCHAR(MAX),@q1XML)INSERT INTO #TempTableEXEC dbo.uspParseXML @strXML, N'System'SET @strXML = CONVERT(NVARCHAR(MAX),@q2XML)INSERT INTO #TempTableEXEC dbo.uspParseXML @strXML, N'System'SET @strXML = CONVERT(NVARCHAR(MAX),@q3XML)INSERT INTO #TempTableEXEC dbo.uspParseXML @strXML, N'System'SET @strXML = CONVERT(NVARCHAR(MAX),@q4XML)INSERT INTO #TempTableEXEC dbo.uspParseXML @strXML, N'System'SET @strXML = CONVERT(NVARCHAR(MAX),@userxml)INSERT INTO #TempTableEXEC dbo.uspParseXML @strXML, N'LoanData'SET @strXML = CONVERT(NVARCHAR(MAX),@buyerxml)INSERT INTO #TempTableEXEC dbo.uspParseXML @strXML, N'BuyerData'SET @strXML = CONVERT(NVARCHAR(MAX),@calcxml)INSERT INTO #TempTableEXEC dbo.uspParseXML @strXML, N'CalcData'SELECT * FROM #TempTable AS tt/* These skip the recursion */IF OBJECT_ID('tempdb..#SimpleTable') IS NOT NULLDROP TABLE #SimpleTableCREATE 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 #SimpleTableEXEC dbo.uspParseXML_NO_CTE @strXML, N'System'SET @strXML = CONVERT(NVARCHAR(MAX),@q1XML)INSERT INTO #SimpleTableEXEC dbo.uspParseXML_NO_CTE @strXML, N'System'SET @strXML = CONVERT(NVARCHAR(MAX),@q2XML)INSERT INTO #SimpleTableEXEC dbo.uspParseXML_NO_CTE @strXML, N'System'SET @strXML = CONVERT(NVARCHAR(MAX),@q3XML)INSERT INTO #SimpleTableEXEC dbo.uspParseXML_NO_CTE @strXML, N'System'SET @strXML = CONVERT(NVARCHAR(MAX),@q4XML)INSERT INTO #SimpleTableEXEC dbo.uspParseXML_NO_CTE @strXML, N'System'SET @strXML = CONVERT(NVARCHAR(MAX),@userxml)INSERT INTO #SimpleTableEXEC dbo.uspParseXML_NO_CTE @strXML, N'LoanData'SET @strXML = CONVERT(NVARCHAR(MAX),@buyerxml)INSERT INTO #SimpleTableEXEC dbo.uspParseXML_NO_CTE @strXML, N'BuyerData'SET @strXML = CONVERT(NVARCHAR(MAX),@calcxml)INSERT INTO #SimpleTableEXEC dbo.uspParseXML_NO_CTE @strXML, N'CalcData' SELECT * FROM #SimpleTable AS tt[/code]The procedure:[code="sql"]CREATE PROCEDURE [dbo].[uspParseXML] @strXML AS XML ,@rootnode NVARCHAR(255)ASBEGIN 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[/code][code="sql"]CREATE PROCEDURE [dbo].[uspParseXML_NO_CTE] @strXML AS XML ,@rootnode NVARCHAR(255)ASBEGIN 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[/code]I also added some indexes to the LoanDetail table[code="sql"]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]GOCREATE 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[/code]Tue, 05 Feb 2013 00:28:26 GMTSteven Willishttp://www.sqlservercentral.com/Forums/Topic1414958-338-1.aspxI get these errors when I run your SQL.[code="plain"](780 row(s) affected)Msg 207, Level 16, State 1, Line 792Invalid column name 'questionnbr'.Msg 207, Level 16, State 1, Line 792Invalid column name 'questionnbr'.Msg 207, Level 16, State 1, Line 792Invalid column name 'qvalue'.[/code]Can't help if I can't get it to run.Mon, 04 Feb 2013 18:06:43 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1414958-338-1.aspxI 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,PrabhuMon, 04 Feb 2013 10:58:19 GMTprabhu.sthttp://www.sqlservercentral.com/Forums/Topic1414958-338-1.aspxHI,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,PrabhuSat, 02 Feb 2013 12:04:54 GMTprabhu.st