Need help with XML (TSQL statements) please.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need help with XML (TSQL statements) please.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-12-06 : 13:14:53

Hi,

I am not familiar with XML codes but I have the tasks with the folowing requirements need to comple and I wonder
if you have some good samples codes or can help me. SQL 2008.

Thanks so much in advance.

Tasks:
Create a stored procedures with the following requirements:

For each of the Loan numbers in xml

Execute the sql statements.
If any of the xml statement failed for that loan, roll back all the sql codes just for that loan. Then insert the LoanNum and the sql statement in a temporary table.

Continue on to the next loan num

When you are done executing all the statements for all the loans, return the temp table.

Here is the file name: TestXML.xml

<?xml version="1.0" encoding="UTF-8"?>
<PoolID Name="Huntington 062510">
<LoanTransactions LoanNumber="123456">
<SqlInsertStatement>insert KondaurData (LoanNum, ServicerID, PoolNum, BorrLast, BorrFirst, BorrSSN, Occupancy, CurrBalance, CurrIntRate, FirstPayDate, PropertyAddr1, PropertyAddr2, PropertyCity, PropertyState, PropertyZip, PropertyCounty) values ('123333', '113613', 'Huntington 062510', 'FORTIER', 'RONALD L', '306-24-1650', '', '122580.36', '8.85', '8/1/2006', '46 Clare St', '', 'Lowell', 'MA', '1854', 'MIDDLESEX')</SqlInsertStatement>
<SqlInsertStatement>insert BorrData (LoanNum, BorrFICO) values ('123333', '659')</SqlInsertStatement>
<SqlInsertStatement>insert ExitInfo (LoanNum, ListedFlag, ListedPrice, ListingURL) values ('123333', '', '0.00', '')</SqlInsertStatement>
<SqlInsertStatement>insert AppraisalData (LoanNum, OrigApprAmt) values ('123333', '168000.00')</SqlInsertStatement>
</LoanTransactions>
<LoanTransactions LoanNumber="789456">
<SqlInsertStatement>insert KondaurData (LoanNum, ServicerID, PoolNum, BorrLast, BorrFirst, BorrSSN, Occupancy, CurrBalance, CurrIntRate, FirstPayDate, PropertyAddr1, PropertyAddr2, PropertyCity, PropertyState, PropertyZip, PropertyCounty) values ('123334', '11612', 'Huntington 062510', 'Olivo', 'Ann', '022-44-4885', 'OO', '500204.13', '6.75', '1/1/2006', '193 John Wise Ave', '', 'Essex', 'MA', '92108', 'Essex')</SqlInsertStatement>
<SqlInsertStatement>insert BorrData (LoanNum, BorrFICO) values ('123334', '552')</SqlInsertStatement>
<SqlInsertStatement>insert ExitInfo (LoanNum, ListedFlag, ListedPrice, ListingURL) values ('123334', 'Yes', '475000.00', 'http://www.zillow.com/homedetails/193-John-Wise-Ave-Essex-MA-01929/56917844_zpid/')</SqlInsertStatement>
<SqlInsertStatement>insert AppraisalData (LoanNum, OrigApprAmt) values ('123334', '635000.00')</SqlInsertStatement>
</LoanTransactions>
</PoolID>

Thanks so much for your help.

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-12-06 : 22:41:04

You may get better insight through the link,

http://www.sql-programmers.com/Blog/tabid/153/EntryId/10/XML-Data-Type-in-SQL-Server-2005.aspx

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-12-07 : 13:36:45

Hi,

I need to pass in the @filename into SP instead of hard code but this
is a XML datatype and I can't seem to figure out. Any help would greatly appeciate.

IF OBJECT_ID('[v2.spLoanImportProcess]', 'p') IS NOT NULL
DROP PROCedure [v2.spLoanImportProcess]
GO

CREATE PROCedure [v2.spLoanImportProcess]
(
@FileName VARCHAR(200)
)
AS
/***********************************************************************************************
**
** Modifications:
** ----------------------------------
** Date: Author: Reasons:
** ------------+-----------------------------------------------------------------------
**
**
***********************************************************************************************/
SET nocount ON
SET XACT_ABORT ON

-- Cursor variables
DECLARE @LoanNumber INT,
@SqlStatements XML,
@xml XML,
@ErrorLog VARCHAR(MAX),
@sql NVARCHAR(MAX)

IF OBJECT_ID('Tempdb..#ErrorLog', 'u') IS NOT NULL
DROP TABLE #ErrorLog;

CREATE TABLE #ErrorLog
(
LoanNum VARCHAR(10),
ErrorLog xml
)

-- Need dynamic pass in @FileName.
SELECT @xml = x.y FROM OPENROWSET( BULK @FileName, SINGLE_CLOB ) x(y)

SELECT @xml = x.y FROM OPENROWSET( BULK 'c:\ImportData.xml', SINGLE_CLOB ) x(y)

-- SELECT @xml
-- x.y FROM OPENROWSET( BULK c:\ImportData.xml, SINGLE_CLOB ) x(y)

-- Create a cursor to run through each loan
DECLARE loans_cursor CURSOR FAST_FORWARD LOCAL FOR

SELECT x.y.value('@LoanNumber', 'VARCHAR(10)') AS LoanNumber,
x.y.query('SqlInsertStatement' ) AS SqlStatements
FROM @xml.nodes('PoolID/LoanTransactions') x(y)

OPEN loans_cursor

FETCH NEXT FROM loans_cursor INTO @LoanNumber, @SqlStatements

WHILE (@@fetch_status = 0)
BEGIN

-- SELECT @LoanNumber, @SqlStatements
DECLARE sql_cursor CURSOR FAST_FORWARD LOCAL FOR

SELECT x.y.value('.', 'NVARCHAR(MAX)' ) AS SqlStatements
FROM @SqlStatements.nodes('SqlInsertStatement') x(y)

OPEN sql_cursor

BEGIN TRAN
BEGIN TRY
FETCH NEXT FROM sql_cursor INTO @sql

WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @ErrorLog = @sql
--SELECT @XMLFile
EXECute (@sql)

FETCH NEXT FROM sql_cursor INTO @sql
END

COMMIT TRAN
END TRY

BEGIN CATCH
DECLARE @Err INT
SET @Err = @@ERROR

IF (@Err <> 0)
BEGIN
ROLLBACK TRAN

INSERT dbo.#ErrorLog (LoanNum, ErrorLog)
SELECT @LoanNumber AS 'LoanNum', @ErrorLog AS 'SQLStm'

END
END CATCH

CLOSE sql_cursor
DEALLOCATE sql_cursor

FETCH NEXT FROM loans_cursor INTO @LoanNumber, @SqlStatements
END

CLOSE loans_cursor
DEALLOCATE loans_cursor

-- Return results set.
SELECT *
FROM #ErrorLog;
GO

quote:
Originally posted by sql-programmers

You may get better insight through the link,

http://www.sql-programmers.com/Blog/tabid/153/EntryId/10/XML-Data-Type-in-SQL-Server-2005.aspx

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-12-07 : 16:27:10

I am try to execute the SP below but getting errors and not sure how to pass in XML code to sp. Can you point out what wrong and how to fix it.

Thanks.

-- new requirements:
-- Users will pass in xml data instead retrive from the text file.

/*

-- Testing...

--Error

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '123333'.

--Executing

EXECute [v2.spLoanImportProcess]
@XML =
'
<?xml version="1.0" encoding="UTF-8"?>
<PoolID Name="Huntington 062510">
<LoanTransactions LoanNumber="123333">
<SqlInsertStatement>insert KondaurData (LoanNum, ServicerID, PoolNum, BorrLast, BorrFirst, BorrSSN, Occupancy, CurrBalance, CurrIntRate, FirstPayDate, PropertyAddr1, PropertyAddr2, PropertyCity, PropertyState, PropertyZip, PropertyCounty) values ('123333', '113613', 'Huntington 062510', 'FORTIER', 'RONALD L', '306-24-1650', '', '122580.36', '8.85', '8/1/2006', '46 Clare St', '', 'Lowell', 'MA', '1854', 'MIDDLESEX')</SqlInsertStatement>
<SqlInsertStatement>insert BorrData (LoanNum, BorrFICO) values ('123333', '659')</SqlInsertStatement>
<SqlInsertStatement>insert ExitInfo (LoanNum, ListedFlag, ListedPrice, ListingURL) values ('123333', '', '0.00', '')</SqlInsertStatement>
<SqlInsertStatement>insert AppraisalData (LoanNum, OrigApprAmt) values ('123333', '168000.00')</SqlInsertStatement>
</LoanTransactions>
<LoanTransactions LoanNumber="123334">
<SqlInsertStatement>insert KondaurData (LoanNum, ServicerID, PoolNum, BorrLast, BorrFirst, BorrSSN, Occupancy, CurrBalance, CurrIntRate, FirstPayDate, PropertyAddr1, PropertyAddr2, PropertyCity, PropertyState, PropertyZip, PropertyCounty) values ('123334', '11612', 'Huntington 062510', 'Olivo', 'Ann', '022-44-4885', 'OO', '500204.13', '6.75', '1/1/2006', '193 John Wise Ave', '', 'Essex', 'MA', '92108', 'Essex')</SqlInsertStatement>
<SqlInsertStatement>insert BorrData (LoanNum, BorrFICO) values ('123334', '552')</SqlInsertStatement>
<SqlInsertStatement>insert ExitInfo (LoanNum, ListedFlag, ListedPrice, ListingURL) values ('123334', 'Yes', '475000.00', 'http://www.zillow.com/homedetails/193-John-Wise-Ave-Essex-MA-01929/56917844_zpid/')</SqlInsertStatement>
<SqlInsertStatement>insert AppraisalData (LoanNum, OrigApprAmt) values ('123334', '635000.00')</SqlInsertStatement>
</LoanTransactions>
</PoolID>
'

*/;

IF OBJECT_ID('[v2.spLoanImportProcess]', 'p') IS NOT NULL
DROP PROCedure [v2.spLoanImportProcess]
GO

CREATE PROCedure [v2.spLoanImportProcess]
(
@XML XML
)
AS
/***********************************************************************************************
**
** Modifications:
** ----------------------------------
** Date: Author: Reasons:
** ------------+-----------------------------------------------------------------------
**
**
***********************************************************************************************/
SET nocount ON
SET XACT_ABORT ON

-- Cursor variables
DECLARE @IsFileExist TINYINT,
@LoanNumber INT,
@SqlStatements XML,
--@xml XML,
@ErrorLog VARCHAR(MAX),
@sql NVARCHAR(MAX)

IF OBJECT_ID('Tempdb..#ErrorLog', 'u') IS NOT NULL
DROP TABLE #ErrorLog;

CREATE TABLE #ErrorLog
(
LoanNum VARCHAR(10),
ErrorLog xml
)

--EXECute Master.dbo.xp_FileExist 'c:\ImportData.xml', @IsFileExist OUT

--IF (@IsFileExist = 0)
-- BEGIN
-- EXECute Master.dbo.xp_cmdshell "@echo The ImportData.xml file does not exist in c:\ImportData.xml directory."
-- END

--SELECT @xml = x.y FROM OPENROWSET( BULK 'c:\ImportData.xml', SINGLE_CLOB ) x(y)

-- SELECT @xml
-- x.y FROM OPENROWSET( BULK c:\ImportData.xml, SINGLE_CLOB ) x(y)

-- Create a cursor to run through each loan
DECLARE loans_cursor CURSOR FAST_FORWARD LOCAL FOR

SELECT x.y.value('@LoanNumber', 'VARCHAR(10)') AS LoanNumber,
x.y.query('SqlInsertStatement' ) AS SqlStatements
FROM @xml.nodes('PoolID/LoanTransactions') x(y)

OPEN loans_cursor

FETCH NEXT FROM loans_cursor INTO @LoanNumber, @SqlStatements

WHILE (@@fetch_status = 0)
BEGIN

-- SELECT @LoanNumber, @SqlStatements
DECLARE sql_cursor CURSOR FAST_FORWARD LOCAL FOR

SELECT x.y.value('.', 'NVARCHAR(MAX)' ) AS SqlStatements
FROM @SqlStatements.nodes('SqlInsertStatement') x(y)

OPEN sql_cursor

BEGIN TRAN
BEGIN TRY
FETCH NEXT FROM sql_cursor INTO @sql

WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @ErrorLog = @sql
--SELECT @XMLFile
EXECute (@sql)

FETCH NEXT FROM sql_cursor INTO @sql
END

COMMIT TRAN
END TRY

BEGIN CATCH
DECLARE @Err INT
SET @Err = @@ERROR

IF (@Err <> 0)
BEGIN
ROLLBACK TRAN

INSERT dbo.#ErrorLog (LoanNum, ErrorLog)
SELECT @LoanNumber AS 'LoanNum', @ErrorLog AS 'SQLStm'

END
END CATCH

CLOSE sql_cursor
DEALLOCATE sql_cursor

FETCH NEXT FROM loans_cursor INTO @LoanNumber, @SqlStatements
END

CLOSE loans_cursor
DEALLOCATE loans_cursor

-- Return results set.
SELECT *
FROM #ErrorLog;

--Delete the file after is done.
--EXECute Master.dbo.xp_cmdshell "IF EXIST c:\ImportData.xml DEL c:\ImportData.xml"
GO

quote:
Originally posted by sql-programmers

You may get better insight through the link,

http://www.sql-programmers.com/Blog/tabid/153/EntryId/10/XML-Data-Type-in-SQL-Server-2005.aspx

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources