Importing XML Files
Peter Newman
SQL 2005
I am having a problem trying to get this Stored Procedure to import an XML
File into a table
The errors I am getting when executing the stored procedure are;
The XML parse error 0xc00ce556 occurred on line number 1, near the XML text
"c:\temp\AruddReport.xml".
Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1
The error description is 'Invalid at the top level of the document.'.
Msg 8179, Level 16, State 5, Procedure sp_DBFMImportAruddXml, Line 21
Could not find prepared statement with handle 0.
The statement has been terminated.
Msg 6607, Level 16, State 3, Procedure sp_xml_removedocument, Line 1
sp_xml_removedocument: The value supplied for parameter number 1 is invalid.
Stored Procedure
USE [DebBacsFM]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_DBFMImportAruddXml]
@ARUDDFILE NTEXT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @iDOC INT
EXEC sp_xml_preparedocument @idoc OUTPUT, @ARUDDFILE
Declare @import TABLE (originalProcessingDate varchar(10),
valueOf varchar(11),
ref varchar(18))
INSERT INTO @import(originalProcessingDate, valueOf, ref )
select originalProcessingDate,
valueOf,
ref
FROM OPENXML( @idoc,
'BACSDocument/Data/ARUDD/Advice/OriginatingAcountRecords/OriginatingAcountRecord/ReturnedDebitItem', 2)
WITH (originalProcessingDate varchar(10) 'originalProcessingDate',
valueOf varchar(11) 'valueOf',
ref varchar(18) 'ref')
EXEC sp_xml_removedocument @idoc
SELECT * FROM @import
END
XML File
<?xml version="1.0" encoding="ISO-8859-1"?>
<!-- Generated by Oracle Reports version 10.1.2.3.0 -->
<BACSDocument xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="newbacs-advices.xsd">
<Data>
<ARUDD>
<Header reportType="REFT0000" adviceNumber="999"
currentProcessingDate="2009-06-02"></Header>
<AddresseeInformation name="TEST COMPANY"></AddresseeInformation>
<ServiceLicenseInformation userName="TEST COMPANY"
userNumber="000000"></ServiceLicenseInformation>
<Advice>
<OriginatingAccountRecords>
<OriginatingAccountRecord>
<OriginatingAccount name="NEW COMPANY" number="12345678"
sortCode="123456" type="0" bankName="SOME BANK PLC" branchName="SOME
STREET"></OriginatingAccount>
<ReturnedDebitItem ref="111111111111111111" transCode="17"
returnCode="0000" returnDescription="HELLO"
originalProcessingDate="2009-05-29" valueOf="1.00"
currency="GBP"><PayerAccount number="99999999" ref="MYREF" name="MR SMITH"
sortCode="11-11-11" bankName="MY BANK" branchName="MY
TOWN"></PayerAccount></ReturnedDebitItem>
<ReturnedDebitItem ref="111111111111111111" transCode="17"
returnCode="0000" returnDescription="HELLO"
originalProcessingDate="2009-05-29" valueOf="1.00"
currency="GBP"><PayerAccount number="99999999" ref="MYREF" name="MR SMITH"
sortCode="11-11-11" bankName="MY BANK" branchName="MY
TOWN"></PayerAccount></ReturnedDebitItem>
<ReturnedDebitItem ref="111111111111111111" transCode="17"
returnCode="0000" returnDescription="HELLO"
originalProcessingDate="2009-05-29" valueOf="1.00"
currency="GBP"><PayerAccount number="99999999" ref="MYREF" name="MR SMITH"
sortCode="11-11-11" bankName="MY BANK" branchName="MY
TOWN"></PayerAccount></ReturnedDebitItem>
<Totals numberOf="3" valueOf="3.00" currency="GBP"></Totals>
</OriginatingAccountRecord>
</OriginatingAccountRecords>
</Advice>
</ARUDD>
</Data><SignatureMethod>Vanilla</SignatureMethod><Signature>MIIJ7QYJKoZIhvcNAQcCoIIJ3jCCCdoCAQExCzAJBgUrDgMCGgUAMAsGCSqGSIb3DQEHAaCCCD4w
</Signature></BACSDocument>
The XML file is supplied by a third party. Can anyone assist with this?.
Thanks in advance
Tom Moreau
You can't feed it a file name. It expects the contents of the file in that
stored proc parameter. That said, you can use OPENROWSET to bring the file
into a temp table or table variable and then use it:
INSERT INTO T
SELECT xCol
FROM (SELECT *
FROM OPENROWSET (BULK 'C:\MyFile\xmlfile.xml', SINGLE_CLOB)
AS xCol) AS R(xCol)
Also, XML is a native datatype in SQL 2005. You don't need to use OPENXML.
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Peter Newman" <Peter...@discussions.microsoft.com> wrote in message
news:58C1EA6A-4729-4896...@microsoft.com...
Peter Newman
Hi Tom,
I have tried your example but come up with another error.
Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "\\SERVER\XMLREPORTS\ARUDDReport.xml"
could not be opened. Operating system error code 5(error not found).
After looking up this error, I have checked that the SQL Server Agent is
running under an administrator profile and that it has full control in the
\\SERVER\XMLREPORTS directory. On the SQL Server both TCIP and named pipes
are enabled. Can you think what the problem may be ?
Erland Sommarskog
Peter Newman (Peter...@discussions.microsoft.com) writes:
> I have tried your example but come up with another error.
>
> Msg 4861, Level 16, State 1, Line 1
> Cannot bulk load because the file "\\SERVER\XMLREPORTS\ARUDDReport.xml"
> could not be opened. Operating system error code 5(error not found).
>
>
> After looking up this error, I have checked that the SQL Server Agent is
> running under an administrator profile and that it has full control in
> the \\SERVER\XMLREPORTS directory. On the SQL Server both TCIP and
> named pipes are enabled. Can you think what the problem may be ?
is locked by another application.
I don't remember exactly which permission that applies here, but I would
expect your own permissions to apply, if you are logged as a Windows login.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Peter Newman
Erland, Thansk for that, I seem to have sorted that problem out only to run
into another. from the suggestions made and sample coding i have found, I
have come up witht he following code
CREATE TABLE #WorkingTable
(Data XML)
INSERT INTO #WorkingTable
SELECT * FROM OPENROWSET (BULK '\\sql1\SQL_Backup\ARUDDReport.xml',
SINGLE_BLOB) AS data
DECLARE @XML AS XML, @hDoc AS INT
SELECT @XML = Data FROM #WorkingTable
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
SELECT *
FROM OPENXML(@hDoc,
'BACSDocument/Data/ARUDD/Advice/OriginatingAcountRecords/OriginatingAcountRecord/ReturnedDebitItem', 1)
WITH (ref VARCHAR(18) 'ref',
origionalProcessingDate VARCHAR(10) 'origionalProcessingDate',
valueOf VARCHAR(10) 'valueOf'
)
EXEC sp_xml_removedocument @hDoc
DROP TABLE #WorkingTable
There are no errors being reported, yet no data is being returned either.
Can some one have a look at this and help me out, in simple laymans terms
please
Erland Sommarskog
> Erland, Thansk for that, I seem to have sorted that problem out only to
> run into another. from the suggestions made and sample coding i have
> found, I have come up witht he following code
>
> CREATE TABLE #WorkingTable
> (Data XML)
> INSERT INTO #WorkingTable
> SELECT * FROM OPENROWSET (BULK '\\sql1\SQL_Backup\ARUDDReport.xml',
> SINGLE_BLOB) AS data
> DECLARE @XML AS XML, @hDoc AS INT
> SELECT @XML = Data FROM #WorkingTable
> EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
> SELECT *
> FROM OPENXML(@hDoc,
> 'BACSDocument/Data/ARUDD/Advice/OriginatingAcountRecords/OriginatingAcountRecord/ReturnedDebitItem', 1)
> WITH (ref VARCHAR(18) 'ref',
> origionalProcessingDate VARCHAR(10) 'origionalProcessingDate',
> valueOf VARCHAR(10) 'valueOf'
> )
> EXEC sp_xml_removedocument @hDoc
> DROP TABLE #WorkingTable
>
> There are no errors being reported, yet no data is being returned either.
> Can some one have a look at this and help me out, in simple laymans terms
> please
There are two problems. Or three. The first is that "account" is
spelt with two "c" in English. The second is that to refer to attributes
you need to prefix them with @, or else the are assumed to be element
names.
The third problem is that you should not use OPENXML at all, but .nodes
and .values, the xml type methods. They are more efficient and there is
no hassle with prepare/close document. Here is how your example would
read with .nodes() and .value():
SELECT T.c.value('@ref', 'varchar(10)'),
T.c.value('@origionalProcessingDate', 'varchar(10)'),
T.c.value('@valueOf', 'varchar(10)')
FROM @x.nodes(
'/BACSDocument/Data/ARUDD/Advice/OriginatingAccountRecords/OriginatingAccountRecord/ReturnedDebitItem') AS T(c)
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
-----------------------------------------------------------------------------
Less Spam Better enjoyable experience
Visit : news://spacesst.com