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

SSCrazy Eights

Points: 9893

February 5, 2013 at 12:28 am

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

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

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

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

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(

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

@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