February 2, 2013 at 12:04 pm
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
February 4, 2013 at 10:58 am
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
February 4, 2013 at 6:06 pm
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.
My thought question: Have you ever been told that your query runs too fast?
My advice:
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?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
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
(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
February 5, 2013 at 4:28 am
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
February 5, 2013 at 4:30 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply