OpenRowset - SQL Server Forums

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)

OpenRowset

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2012-05-13 : 12:06:29

I and to create a stored procedure to read a XML file and extract certain data to insert into a table. The problem is that the xml files are generated by a third party. I have include my TSQL so far

can anyone help

Declare @File varchar(200) = 'C:\XML Reports Master Copy\143352380-PE_UDD_USER_COPY20120316241904.xml'

DECLARE @x xml;

SET @x = (SELECT * FROM OPENROWSET(BULK ' + @File + ', SINGLE_BLOB) AS x);
WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema-instance' AS q1)
/* HEADER */
SELECT
T1.c1.value('@reportType', 'varchar(10)') AS [reportType],
T1.c1.value('@adviceNumber', 'varchar(10)') AS [adviceNumber],
T1.c1.value('@currentProcessingDate', 'varchar(10)') AS [currentProcessingDate]
INTO #ARUDDHEADER
FROM
@x.nodes('//Data/ARUDD/Header') AS T1(c1)

/* ServiceLicenseInformation */
SELECT
T1.c1.value('@userName', 'varchar(50)') AS [userName],
T1.c1.value('@userNumber', 'varchar(6)') AS [userNumber]
INTO #ARUDDServiceLicenseInformation
FROM
@x.nodes('//Data/ARUDD/ServiceLicenseInformation') AS T1(c1)

/* OriginatingAccountRecords */
SELECT
T1.c1.value('@name', 'varchar(50)') AS [name],
T1.c1.value('@number', 'varchar(8)') AS [number],
T1.c1.value('@sortCode', 'varchar(6)') AS [sortCode],
T1.c1.value('@type', 'varchar(2)') AS [type],
T1.c1.value('@bankName', 'varchar(50)') AS [bankName],
T1.c1.value('@branchName', 'varchar(50)') AS [branchName]
INTO #ARUDOriginatingAccountRecords
FROM
@x.nodes('//Data/ARUDD/Advice/OriginatingAccountRecords/OriginatingAccountRecord/OriginatingAccount,') AS T1(c1)

/* ReturnedDebitItem */
SELECT
T1.c1.value('@ref', 'varchar(18)') AS [RTNref],
T1.c1.value('@transCode', 'varchar(2)') AS [RTNtransCode],
T1.c1.value('@returnCode', 'varchar(10)') AS [RTNreturnCode],
T1.c1.value('@returnDescription', 'varchar(50)') AS [RTNreturnDescription],
T1.c1.value('@originalProcessingDate', 'varchar(10)') AS [RTNoriginalProcessingDate],
T1.c1.value('@valueOf', 'varchar(13)') AS [RTNvalueOf],
T1.c1.value('@currency', 'varchar(4)') AS [RTNcurrency],
T1.c1.value('@number', 'varchar(8)') AS [ORIGnumber],
T1.c1.value('@ref', 'varchar(18)') AS [ORIGref],
T1.c1.value('@name', 'varchar(18)') AS [ORIGname],
T1.c1.value('@sortCode', 'varchar(10)') AS [ORIGsortCode],
T1.c1.value('@bankName', 'varchar(50)') AS [ORIGbankName],
T1.c1.value('@branchName', 'varchar(50)') AS [ORIGbranchName]
INTO #ARUDDReturnedDebitItem
FROM
@x.nodes('//Data/ARUDD/Advice/OriginatingAccountRecords/OriginatingAccountRecord/ReturnedDebitItem,') AS T1(c1)
OUTER APPLY
T1.c1.nodes('./PayerAccount') AS T2(c1)

SELECT * FROM #ARUDDHEADER
SELECT * FROM #ARUDDServiceLicenseInformation
SELECT * FROM #ARUDOriginatingAccountRecords
SELECT * FROM #ARUDDReturnedDebitItem

DROP TABLE #ARUDDHEADER
DROP TABLE #ARUDDServiceLicenseInformation
DROP TABLE #ARUDOriginatingAccountRecords
DROP TABLE #ARUDDReturnedDebitItem

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-13 : 13:03:00

What is the problem/error message you are getting? Can you also post a sample of the data that is causing the issue?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-13 : 14:21:54

if the issue is with the data you get, you've to post part of xml document to give us an idea of how data is structured in it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Pete_N
Posting Yak Master

181 Posts

Posted - 2012-05-13 : 14:38:21

quote:
Originally posted by sunitabeck

What is the problem/error message you are getting? Can you also post a sample of the data that is causing the issue?

the error i get is

Msg 4860, Level 16, State 1, Line 6
Cannot bulk load. The file " + @File + " does not exist.

if i hardcode the filename in like this

SET @x = (SELECT * FROM OPENROWSET(BULK 'C:\XML Reports Master Copy\143352380-PE_UDD_USER_COPY20120316241904.xml', SINGLE_BLOB) AS x);
WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema-instance' AS q1)

the rest of my query executes fine and there are no errors

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-13 : 14:56:22

quote:
Originally posted by Pete_N

quote:
Originally posted by sunitabeck

What is the problem/error message you are getting? Can you also post a sample of the data that is causing the issue?

the error i get is

Msg 4860, Level 16, State 1, Line 6
Cannot bulk load. The file " + @File + " does not exist.

if i hardcode the filename in like this

SET @x = (SELECT * FROM OPENROWSET(BULK 'C:\XML Reports Master Copy\143352380-PE_UDD_USER_COPY20120316241904.xml', SINGLE_BLOB) AS x);
WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema-instance' AS q1)

the rest of my query executes fine and there are no errors

you've to use sp_executesql and execute OPENROWSET query as a dynamicquery if Filename is coming from a variable.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Pete_N
Posting Yak Master

181 Posts

Posted - 2012-05-13 : 15:03:16

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-13 : 15:25:43

see

http://support.microsoft.com/kb/314520

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources