Passing an XML file to a stored procedure

What's the datatype of your @XMLDoc parameter ?
are you trying to pass a file name or a varchar/nvarchar string that contains the xml ?

if the xml text is using UTF-8 encoding, you need to pass the string as a varchar datatype
if it uses UTF-16, you must use nvarchar instead

It is probably going to be text (?) ... the file is really long.  It is a credit report returned in a standardized XML format...

I do get the file information from an http post.  Can I just use the URL that I post to?

Thanks for your help!

>>Can I just use the URL that I post to?<<
No you can't
You'll have to pass the data as varchar (up to 8000 characters), nvarchar (up to 4000 characters) or text/ntext if it's bigger

Passint text/ntext parameters is quite compicated, i'll try to provide sample code if you need some.

Cheers

Hilaire

this question might help:

https://www.experts-exchange.com/questions/21086593/Accessing-XML-document-FROM-T-SQL.html

I would really appreciate some code.  I have been working on this for over a week.  I have been using the Guru's Guide to SQL Server Stored Procedures, XML and HTML as guide.  Maybe I should go back to the beginning and describe what I am trying to do in more detail.

I am sending information via https post to a third party and receiving the XML file back.  I have all of that working fine.  The XML file I am receiving back is large and I will definately have to pass is as text/ntext.   The information that is being returned is a credit report (with a PDF embedded), and I need to pick through certain information and save it to my DB.  Certain people may have up to 15+ open accounts with creditors.

I am wondering if I should be using the SQL XMLBulkLoad component.  I am new to all of this...what is your opinion on the most efficient way to do this?  

Thanks again for your help!

Susan

>>I am wondering if I should be using the SQL XMLBulkLoad component. <<
You should not have to.  As Hillaire has pointed out you can pass a text parameter.

Unfortunately, this:
"But if I try to pass the XML file as a parameter, it isn't working."
Does not help us very much.  Can you elaborate, please?  Is there an error, if so what?  Does it give you the wrong results, if so post them and the desired result.

Also, post a sample of the Xml document, especially important as Hillaire implied is the encoding.

Here is the code that I am using....must not be doing something correctly....I know I need to get the @XMLDoc variable pointed to the file XML document somehow.  
CREATE PROCEDURE sp_Credit3
@XMLDoc text

AS


DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc output, @XMLDoc
INSERT Liabilities (CreditResponseID)
SELECT id
FROM OPENXML(@hdoc, '/RESPONSE/CREDIT_RESPONSE/CreditResponseID', 1)
WITH (id varchar(20) '@id')

EXEC sp_xml_removedocument @hDoc
GO


The XML file is large....I will point you to it

http://m2lcallcenter.com/response.xml


Thank you!

here is the proc to load an xml file by passin gthe file location:
--########################################
CREATE  PROCEDURE dbo.sp_xmlfile_preparedocument @hdoc Integer Output,
                                              @xmlfile varchar(1000) = NULL
AS
--
--    Description:
--
--        Utility procedure to invoke sp_xml_preparedocument from a file rather than text

Begin

SET NOCOUNT ON    

Declare @i int,
        @size int,
        @blocks int,
        @varcnt int,
        @qm char(1),
        @bperv integer,
        @cmdsrc varchar(8000),
        @cmdsrc2 varchar(8000),
        @cmdsrc3 varchar(8000),
        @CRLF Varchar(2)

Set @CRLF = Char(13) + Char(10)

create table #filedetails
(altname varchar(30),size int,createdate varchar(32),
 createtime varchar(32),lastwrittendt varchar(30),
 lastwrittentime varchar(32),lastaccessdt varchar(30),
 lastaccesstime varchar(32),attributes int)

insert into #filedetails exec master..xp_getfiledetails @xmlfile

set @size=(select size from #filedetails)

drop table #filedetails

Set @bperv = 165
Set @qm = char(39)
Set @blocks=@size/4000+1
Set @varcnt=(@blocks-1)/@bperv+1

Set @cmdsrc = 'Declare @cmdDS varchar(8000), '
Set @cmdsrc = @cmdsrc + '@cmdES varchar(8000), '
Set @i=1
While @i<=@varcnt
  Begin
  Set @cmdsrc = @cmdsrc + '@cmdD' + Convert(Varchar(9),@i) + ' Varchar(8000), '
  Set @cmdsrc = @cmdsrc + '@cmdR' + Convert(Varchar(9),@i) + ' Varchar(8000), '
  Set @cmdsrc = @cmdsrc + '@cmdF' + Convert(Varchar(9),@i) + ' Varchar(8000), '
  Set @cmdsrc = @cmdsrc + '@cmdE' + Convert(Varchar(9),@i) + ' Varchar(8000), '
  Set @i=@i+1
  End    
Set @cmdsrc = @cmdsrc + '@cmdDF varchar(8000), '
Set @cmdsrc = @cmdsrc + '@cmdEF varchar(8000), '
Set @cmdsrc = @cmdsrc + '@i int, @blocks int'

Set @cmdsrc = @cmdsrc + ' SET @blocks=' + Convert(Varchar(9),@blocks)
Set @cmdsrc = @cmdsrc + ' SET @cmdDS='+@qm+'DECLARE @fso int, @fl int, @hr int, @qm char(1), @vp varchar(8000), @vs varchar(8000), '+@qm

Set @cmdsrc = @cmdsrc + ' SET @cmdDF='+@qm+' Set @qm = char(39)'+@qm
Set @cmdsrc = @cmdsrc + ' SET @cmdDF=@cmdDF+'+@qm+' Set @vp = '+@qm+@qm+'DECLARE @Handle int EXEC sp_xml_preparedocument @Handle OUT, '+@qm+@qm+'+@qm'+@qm
Set @cmdsrc = @cmdsrc + ' SET @cmdDF=@cmdDF+'+@qm+' Set @vs = @qm+'+@qm+@qm+' DECLARE he_cur CURSOR GLOBAL FOR SELECT @Handle'+@qm+@qm+@qm
Set @cmdsrc = @cmdsrc + ' SET @cmdDF=@cmdDF+'+@qm+' EXEC @hr = sp_OACreate '+@qm+@qm+'Scripting.FileSystemObject'+@qm+@qm+', @fso OUT '+@qm
Set @cmdsrc = @cmdsrc + ' SET @cmdDF=@cmdDF+'+@qm+'exec sp_oamethod @fso, '+@qm+@qm+'opentextfile'+@qm+@qm+', @fl out, '+@qm+@qm+@xmlfile+@qm+@qm+', 1 '+@qm
Set @i=1
While @i<=@varcnt
  Begin
  Set @cmdsrc = @cmdsrc + ' SET @cmdD' + Convert(Varchar(9),@i) + '='+@qm+@qm
  Set @cmdsrc = @cmdsrc + ' SET @cmdR' + Convert(Varchar(9),@i) + '='+@qm+@qm
  Set @cmdsrc = @cmdsrc + ' SET @cmdF' + Convert(Varchar(9),@i) + '='+@qm+@qm
  Set @cmdsrc = @cmdsrc + ' SET @cmdE' + Convert(Varchar(9),@i) + '='+@qm+@qm
  Set @i=@i+1
  End    
Set @cmdsrc = @cmdsrc + ' SET @i=1'
Set @cmdsrc = @cmdsrc + ' WHILE @i<=@blocks'
Set @cmdsrc = @cmdsrc + ' BEGIN'
Set @cmdsrc2 = ''
Set @i=1
While @i<=@varcnt
  Begin
  If @i <> 1 Set @cmdsrc2 = @cmdsrc2 + ' ELSE'
  If @i <> @varcnt Set @cmdsrc2 = @cmdsrc2 + ' IF @i < ' + Convert(Varchar(9),@i*@bperv+1)
  If @varcnt > 1 Set @cmdsrc2 = @cmdsrc2 + ' BEGIN'  
  Set @cmdsrc2 = @cmdsrc2 + ' SET @cmdD' + Convert(Varchar(9),@i) + '=@cmdD' + Convert(Varchar(9),@i) + '+'+@qm+'@v'+@qm+'+CONVERT(VARCHAR(9),@i)+'+@qm+' varchar(8000), '+@qm
  Set @cmdsrc2 = @cmdsrc2 + ' SET @cmdR' + Convert(Varchar(9),@i) + '=@cmdR' + Convert(Varchar(9),@i) + '+'+@qm+'exec @hr=sp_oamethod @fl,'+@qm+@qm+'read'+@qm+@qm+','+@qm+'+'+@qm+'@v'+@qm+'+CONVERT(VARCHAR(9),@i)+'+@qm+' out,4000 '+@qm
  Set @cmdsrc2 = @cmdsrc2 + ' SET @cmdF' + Convert(Varchar(9),@i) + '=@cmdF' + Convert(Varchar(9),@i) + '+'+@qm+'Set @v'+@qm+'+CONVERT(VARCHAR(9),@i)+'+@qm+'=Replace(@v'+@qm+'+CONVERT(VARCHAR(9),@i)+'+@qm+',@qm,@qm+@qm) '+@qm
  Set @cmdsrc2 = @cmdsrc2 + ' SET @cmdE' + Convert(Varchar(9),@i) + '=@cmdE' + Convert(Varchar(9),@i) + '+'+@qm+'@v'+@qm+'+CONVERT(VARCHAR(9),@i)+'+@qm+'+'+@qm
  If @varcnt > 1 Set @cmdsrc2 = @cmdsrc2 + ' END'
  Set @i=@i+1
  End    
Set @cmdsrc3 = ' SET @i=@i+1'
Set @cmdsrc3 = @cmdsrc3 + ' END'

Set @cmdsrc3 = @cmdsrc3 + ' SET @cmdD' + Convert(Varchar(9),@varcnt) + '=LEFT(@cmdD' + Convert(Varchar(9),@varcnt) + ',len(@cmdD' + Convert(Varchar(9),@varcnt) + ')-1)'

Set @cmdsrc3 = @cmdsrc3 + ' SET @cmdES='+@qm+'exec(@vp+'+@qm
Set @cmdsrc3 = @cmdsrc3 + ' SET @cmdEF='+@qm+'@vs) EXEC @hr = sp_OADestroy @fl EXEC @hr = sp_OADestroy @fso'+@qm
Set @cmdsrc3 = @cmdsrc3 + ' exec( @cmdDS+'
Set @i=1
While @i<=@varcnt
  Begin
  Set @cmdsrc3 = @cmdsrc3 + '@cmdD' + Convert(Varchar(9),@i) + '+'
  Set @i=@i+1
  End    
Set @cmdsrc3 = @cmdsrc3 + '@cmdDF+'
Set @i=1
While @i<=@varcnt
  Begin
  Set @cmdsrc3 = @cmdsrc3 + '@cmdR' + Convert(Varchar(9),@i) + '+'
  Set @i=@i+1
  End    
Set @i=1
While @i<=@varcnt
  Begin
  Set @cmdsrc3 = @cmdsrc3 + '@cmdF' + Convert(Varchar(9),@i) + '+'
  Set @i=@i+1
  End    
Set @cmdsrc3 = @cmdsrc3 + '@cmdES+'
Set @i=1
While @i<=@varcnt
  Begin
  Set @cmdsrc3 = @cmdsrc3 + '@cmdE' + Convert(Varchar(9),@i) + '+'
  Set @i=@i+1
  End    
Set @cmdsrc3 = @cmdsrc3 + '@cmdEF )'

Execute (@cmdsrc+@cmdsrc2+@cmdsrc3)

OPEN GLOBAL he_cur
FETCH he_cur INTO @hdoc
DEALLOCATE GLOBAL he_cur

IF @hdoc IS NULL
    RAISERROR( 'Invalid Handle!', 16, 1 )

End


GO
--##########################################

here is how to call it from your proc:

--###########################################
CREATE PROCEDURE sp_Credit3
@XMLDoc varchar(1000)

AS

--@XMLDoc is now the full path to the xml file

DECLARE @hDoc int
EXEC master..sp_xmlfile_preparedocument @hDoc OUTPUT, @XMLDoc
--EXEC sp_xml_preparedocument @hDoc output, @XMLDoc
INSERT Liabilities (CreditResponseID)
SELECT id
FROM OPENXML(@hdoc, '/RESPONSE/CREDIT_RESPONSE/CreditResponseID', 1)
WITH (id varchar(20) '@id')

EXEC sp_xml_removedocument @hDoc
GO

You XPath is not correct, it should be:
/RESPONSE_GROUP/RESPONSE/RESPONSE_DATA/CREDIT_RESPONSE/@CreditResponseID
It returns one value: "DAA00911M01000"

So your stored procedure should look like this:
CREATE PROCEDURE sp_Credit3
@XMLDoc text

AS

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc output, @XMLDoc
INSERT Liabilities (CreditResponseID)
SELECT id
FROM OPENXML(@hdoc, '/RESPONSE_GROUP/RESPONSE/RESPONSE_DATA/CREDIT_RESPONSE/@CreditResponseID', 1)
WITH (id varchar(20) '@id')

EXEC sp_xml_removedocument @hDoc
GO

>>The XML file is large<<
It does not matter how big it is, the text data should take care of it.  What front end language (VB, C#, ??) are you using to pass the Xml document to the stored procedure?

I am using ASP on the front-end.  This is for a web based application.  I feel like this is so far over my head that I am going to have to bury my head in my book.

I appreciate everyone's help with this, and I still have more questions.  I am such a newbie :o) .

How do I pass sp_xmlfile_preparedocument to sp_Credit3 ??  

Because my experience with stored procedures is so limited, I will post the ASP code that I am familiar using.... (this is for something different, but I this is how I am used to calling stored procedures from my ASP page)

<%

Dim sp_NewTotal__varTotal
sp_NewTotal__varTotal = Date()
if(Request("varTotal") <> "") then sp_NewTotal__varTotal = Request("varTotal")

set sp_NewTotal = Server.CreateObject("ADODB.Command")
sp_NewTotal.ActiveConnection = MM_M2LCallcenter_STRING
sp_NewTotal.CommandText = "dbo.sp_NewTotal"
sp_NewTotal.CommandType = 4
sp_NewTotal.CommandTimeout = 0
sp_NewTotal.Prepared = true
sp_NewTotal.Parameters.Append sp_NewTotal.CreateParameter("@RETURN_VALUE", 3, 4)
sp_NewTotal.Parameters.Append sp_NewTotal.CreateParameter("@varTotal", 135, 1,10,sp_NewTotal__varTotal)
set rsNewTotal = sp_NewTotal.Execute
rsNewTotal_numRows = 0

%>


So do I need to use ASP to pass the information from one sp to the other??  I think I am really missing something fundamental here...any pointers/articles would be appreciated...

Thank you again for your help!  I hope that I can one day be good enough at all this to help out someone else!

sp_xmlfile_preparedocument
is called from inside sp_Credit3

pass the full file path to the xml doc as a param to sp_Credit3

FYI: avoid using sp_.... for your procs. a prefix of sp will make sql server look in master for the proc
before the local db....

suzywal,

Here is a complete example that I have tested.

1. I created a table called Liabilities as follows:
CREATE TABLE Liabilities (
      ID [integer IDENTITY (1, 1) NOT NULL,
      CreditResponseID varchar(50) NULL
) ON [PRIMARY]
GO

2. I created the following Stored Procedure:
CREATE PROCEDURE sp_Credit3
@XMLDoc text

AS

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc output, @XMLDoc

INSERT Liabilities (CreditResponseID)
SELECT  id
FROM OPENXML(@hdoc, '/RESPONSE_GROUP/RESPONSE/RESPONSE_DATA/CREDIT_RESPONSE', 1)
WITH (id varchar(20) '@CreditResponseID')

EXEC sp_xml_removedocument @hDoc
GO

3.  I created the ASP code that follows:

<%
Const URL = "http://m2lcallcenter.com/response.xml"
Dim HTTPRequest
Dim XmlResponse
Dim cmd

Set HTTPRequest = Server.CreateObject("Msxml2.ServerXMLHTTP")
HTTPRequest.open "GET", URL, false
HTTPRequest.send

If HTTPRequest.Status <> 200 Then
   Response.Write HTTPRequest.Status
Else
      XmlResponse = HTTPRequest.ResponseText
      Set cmd = Server.CreateObject("ADODB.Command")
      cmd.ActiveConnection = MM_M2LCallcenter_STRING
      cmd.CommandText = "sp_Credit3"
      cmd.CommandType = 4
      cmd.Parameters.Append cmd.CreateParameter("@XMLDoc", 201, 1, 2147483647, XmlResponse)
      cmd.Execute,,128
      Set cmd = Nothing
End If
Set HTTPRequest = Nothing
%>

I trust that clarifies.

Thank you everyone for your help!! I will give it a try!

Thank you!  I have it working.

I have another questions.  There are several instances of the <CREDIT_LIABILITY> tag in my XML file.  Can I loop through all of them using SQL to insert each liability as a new record?  Is this something that the BulkLoad component is for?

Thank you again for your help!

Susan

>>Can I loop through all of them using SQL to insert each liability as a new record? <<
No you do not have to.  The code I posted will insert as many rows as it finds.

Hmmm....There is only one instance of the "<CREDIT_RESPONSE>" tag and the multiple instances of "<CREDIT_LIABILITY>" are within that.  I am only getting the a record for the first liability.

Thank you!

You need to do two things:

1.  Change the XPath to: /RESPONSE_GROUP/RESPONSE/RESPONSE_DATA/CREDIT_RESPONSE/CREDIT_LIABILITY in other words, change:
FROM OPENXML(@hdoc, '/RESPONSE_GROUP/RESPONSE/RESPONSE_DATA/CREDIT_RESPONSE', 1)
To:
FROM OPENXML(@hdoc, '/RESPONSE_GROUP/RESPONSE/RESPONSE_DATA/CREDIT_RESPONSE/CREDIT_LIABILITY', 1)
This will return 72 nodes.

2.  Decide what data (elements or attributes) from the CREDIT_LIABILITY' you need to insert and change the WITH statement and SELECT columns accordingly.  

If it helps, I suggest you post what data your need and where you need to insert it (table name and column names).

Ah!!  Got it.  I am such a newbie :o)

Thank you!

We have all been there.

Hello again -

Not sure if I can add on to my previous post.  I guess I will find out.

Is there any way to take the XML file that I am receiving from the third party and directly use in the stored procedure  to insert the credit without saving the XML file on the server?  Here is what I have so far, but I am receiving this error:


The XML page cannot be displayed
Cannot view XML input using style sheet. Please correct the error and then click the Refresh button, or try again later.


--------------------------------------------------------------------------------

XML document must have a top level element. Error processing resource 'https://m2lcallcenter.com/creditRequest.asp'.
 




<%
  Dim objSrvHTTP
  Dim objXMLDocument
  set objSrvHTTP = Server.CreateObject ("MSXML2.ServerXMLHTTP")
  set objXMLDocument = Server.CreateObject ("Msxml2.DOMDocument.4.0")

   objXMLDocument.async= false
   objXMLDocument.resolveExternals = false
   objXMLDocument.loadXML strXML

   objSrvHTTP.open "POST","https://secure.fncs.com/credit_test/standard/mismo", false
   objSrvHTTP.send objXMLDocument
   Response.ContentType = "text/xml"
 


   '  Response.Write objSrvHTTP.responseXML.xml

' Additional part ... grab the file and run the proc

      Const URL = "https://secure.fncs.com/credit_test/standard/mismo"
      Dim HTTPRequest
      Dim XmlResponse
      Dim cmd

      Set HTTPRequest = Server.CreateObject("Msxml2.ServerXMLHTTP")
      HTTPRequest.open "GET", URL, false
      HTTPRequest.send


      If HTTPRequest.Status <> 200 Then
            Response.Write HTTPRequest.Status
      Else
             XmlResponse = HTTPRequest.ResponseText
             Set cmd = Server.CreateObject("ADODB.Command")
             cmd.ActiveConnection = MM_M2LCallcenter_STRING
             cmd.CommandText = "sp_CreditLiability"
             cmd.CommandType = 4
             cmd.Parameters.Append cmd.CreateParameter("@XMLDoc", 201, 1, 2147483647, XmlResponse)
             cmd.Execute,,128
             Set cmd = Nothing
      End If
      Set HTTPRequest = Nothing

I tried replacing the Const URL = "https://secure.fncs.com/credit_test/standard/mismo"
with ConstURL = responseXML
didn't work.

I would really rather not save the XML files on the server....is that something that I will need to do??

Thank you again!

Susan

>>Is there any way to take the XML file that I am receiving from the third party and directly use in the stored procedure  to insert the credit without saving the XML file on the server?<<
I am not following you.  If you look at my example I do not save the Xml to the server, but rather I pass it directly to the stored procedure.

And yes, this would be best in a new thread.

OK - I have it posted here: https://www.experts-exchange.com/questions/21100129/Passing-an-XML-file-to-a-stored-procedure-Follow-Up-question.html