JDL129
asked on
Need help with XML
I am a new, new, newbie @ XML. I have several XML templates which I need to store in my application or some other convient place and replace the template elements with actual data and send it to where ever it is supposed to go. Is it possible to replace the template elements with variables to which I can assign values as the app runs? Does it need special formatting? Can the templates be stored in procedures? Can the templates be stored in stored procedures in sqlserver 2005 express and the element values passed from the app? The attached XML is an example of what I will be using.
If this sounds like I don't know what I'm doing you are right but I'll bet there is an expert out there in the clouds who can make perfect sense out of it!!!
Thanks,
Jerry
If this sounds like I don't know what I'm doing you are right but I'll bet there is an expert out there in the clouds who can make perfect sense out of it!!!
Thanks,
Jerry
<?xml version=“1.0”?>
<TStream>
<Transaction>
<IpAddress>999.999.999.999</IpAddress>
<IpPort>99999</IpPort>
<MerchantID>MerchantID</MerchantID>
<TerminalID>TerminalID</TerminalID>
<OperatorID>OperatorID</OperatorID>
<TranType>Credit</TranType>
<Duplicate>Override</Duplicate>
<CardType>CardType</CardType>
<TranCode>Sale</TranCode>
<InvoiceNo>InvoiceNo</InvoiceNo>
<RefNo>RefNo</RefNo>
<PartialAuth>PartialAuth</PartialAuth>
<Account>
<Track2>Track2</Track2>
</Account>
<Amount>
<Purchase>Purchase</Purchase>
<Tax>Tax</Tax>
</Amount>
<AVS>
<Address>Address</Address>
<Zip>Zip</Zip>
</AVS>
<CVVData>CVVData</CVVData>
<TerminalName>TerminalName</TerminalName>
<ShiftID>ShiftID</ShiftID>
<Signature>Signature</Signature>
<TranInfo>
<CustomerCode>CustomerCode</CustomerCode>
</TranInfo>
</Transaction>
</TStream>
Note: If magnetic stripe track 2 ABA data is not available, substitute the hand keyed account number and
expiration date in the Account tag as follows:
<Account>
<AcctNo>AcctNo</AcctNo>
<ExpDate>ExpDate</ExpDate>
</Account>.
Creating the XML in SQL is easy, all you do is pass in the values and use the FOR XML clause in the select statement.
See following sample, all you need to do is replace the hardcoded values with variables. The first three are already done in this sample.
See following sample, all you need to do is replace the hardcoded values with variables. The first three are already done in this sample.
DECLARE @xmlResult XML
DECLARE
@IpAddress VARCHAR(100)
,@IpPort VARCHAR(100)
,@MerchantID VARCHAR(100)
SELECT
@IpAddress = '999.999.999.999'
,@IpPort = '99999'
,@MerchantID = 'MerchantID'
SELECT @xmlResult = (
SELECT
@IpAddress AS IpAddress
,@IpPort AS IpPort
,@MerchantID AS MerchantID
,'TerminalID' AS TerminalID --Replace with parameters from here down.
,'OperatorID' AS OperatorID
,'Credit' AS TranType
,'Override' AS Duplicate
,'CardType' AS CardType
,'Sale' AS TranCode
,'InvoiceNo' AS InvoiceNo
,'RefNo' AS RefNo
,'PartialAuth' AS PartialAuth
,'Track2' AS 'Account/Track2'
,'Purchase' AS 'Amount/Purchase'
,'Tax' AS 'Amount/Tax'
,'Address' AS 'AVS/Address'
,'Zip' AS 'AVS/Zip'
,'CVVData' AS CVVData
,'TerminalName' AS TerminalName
,'ShiftID' AS ShiftID
,'Signature' AS [Signature]
,'CustomerCode' AS 'TranInfo/CustomerCode'
FOR XML PATH('Transaction'), ROOT('TStream')
)
SELECT @xmlResult AS 'TheResult'
ASKER
Erik37!!!!!!!!!!
Thanks for the response, looks just like what I'm looking for!!!! How would I parse the response and how would I create a stored procedure from this?
Thanks,
Jerry
Thanks for the response, looks just like what I'm looking for!!!! How would I parse the response and how would I create a stored procedure from this?
Thanks,
Jerry
How is this xml to be used? Are you sending it / using it from your VB6 app or from SQL Server?
ASKER
Erick37!!!!!!!!!!
The attached code is what I came up with to try to create a stored procedure. Below is the error I'm getting:
Msg 156, Level 15, State 1, Procedure sp_CreditSale, Line 4
Incorrect syntax near the keyword 'DECLARE'.
Is this what I should be doing?
Thanks for the help!!!
Jerry
The attached code is what I came up with to try to create a stored procedure. Below is the error I'm getting:
Msg 156, Level 15, State 1, Procedure sp_CreditSale, Line 4
Incorrect syntax near the keyword 'DECLARE'.
Is this what I should be doing?
Thanks for the help!!!
Jerry
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[sp_CreditSale]
-- Add the parameters for the stored procedure here
DECLARE @xmlResult XML
DECLARE
@IpAddress VARCHAR(100)
,@IpPort VARCHAR(100)
,@MerchantID VARCHAR(100)
,@TerminalID VARCHAR(2)
,@Clerk VARCHAR(10)
,@CardType VARCHAR(15)
,@TXNum VARCHAR(10)
,@Total Money
,@Tax Money
,@Address VARCHAR(25)
,@Zip VARCHAR(11)
,@CVVData Varchar(10)
SELECT
@IpAddress = '999.999.999.999'
,@IpPort = '99999'
,@MerchantID = 'MerchantID'
SELECT @xmlResult = (
SELECT
@IpAddress AS IpAddress
,@IpPort AS IpPort
,@MerchantID AS MerchantID
,@TerminalID AS TerminalID --Replace with parameters from here down.
,@Clerk AS OperatorID
,'Credit' AS TranType
,'Override' AS Duplicate
,@CardType AS CardType
,'Sale' AS TranCode
,@TXNum AS InvoiceNo
--,'RefNo' AS RefNo
--,'PartialAuth' AS PartialAuth
--,'Track2' AS 'Account/Track2'
,@Total AS 'Amount/Purchase'
,@Tax AS 'Amount/Tax'
,@Address AS 'AVS/Address'
,@Zip AS 'AVS/Zip'
,@CVVData AS CVVData
--,'TerminalName' AS TerminalName
--,'ShiftID' AS ShiftID
--,'Signature' AS [Signature]
--,'CustomerCode' AS 'TranInfo/CustomerCode'
FOR XML PATH('Transaction'), ROOT('TStream')
)
SELECT @xmlResult AS 'TheResult'
ASKER
Erick37!!!!!!!!!
As to your question as to how I would use this I would bow to your much greater experience. What do you think? How would it work the best?
Thanks,
Jerry
As to your question as to how I would use this I would bow to your much greater experience. What do you think? How would it work the best?
Thanks,
Jerry
Following is the Updated procedure. All you had missing was the "AS" keyword.
If you need help calling this from VB let me know.
If you need help calling this from VB let me know.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_CreditSale]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_CreditSale]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[sp_CreditSale]
@IpAddress VARCHAR(100)
,@IpPort VARCHAR(100)
,@MerchantID VARCHAR(100)
,@TerminalID VARCHAR(2)
,@Clerk VARCHAR(10)
,@CardType VARCHAR(15)
,@TXNum VARCHAR(10)
,@Total Money
,@Tax Money
,@Address VARCHAR(25)
,@Zip VARCHAR(11)
,@CVVData Varchar(10)
AS
DECLARE @xmlResult XML
SELECT @xmlResult = (
SELECT
@IpAddress AS IpAddress
,@IpPort AS IpPort
,@MerchantID AS MerchantID
,@TerminalID AS TerminalID --Replace with parameters from here down.
,@Clerk AS OperatorID
,'Credit' AS TranType
,'Override' AS Duplicate
,@CardType AS CardType
,'Sale' AS TranCode
,@TXNum AS InvoiceNo
--,'RefNo' AS RefNo
--,'PartialAuth' AS PartialAuth
--,'Track2' AS 'Account/Track2'
,@Total AS 'Amount/Purchase'
,@Tax AS 'Amount/Tax'
,@Address AS 'AVS/Address'
,@Zip AS 'AVS/Zip'
,@CVVData AS CVVData
--,'TerminalName' AS TerminalName
--,'ShiftID' AS ShiftID
--,'Signature' AS [Signature]
--,'CustomerCode' AS 'TranInfo/CustomerCode'
FOR XML PATH('Transaction'), ROOT('TStream')
)
SELECT @xmlResult AS 'TheResult'
GO
ASKER
Erik37!!!!!!
Works like a charm!!!!! How do I get the results back in vb6 and parse the return XML?
Thanks for the response!!
Jerry
Works like a charm!!!!! How do I get the results back in vb6 and parse the return XML?
Thanks for the response!!
Jerry
Here is a good example of how to call procedures from VB6. I don't have VB6 installed here, so I cannot test this out:
http://www.codeproject.com/KB/vbscript/simple_sp_vb6.aspx
http://www.codeproject.com/KB/vbscript/simple_sp_vb6.aspx
ASKER
Erick37:
I have a pretty good grasp on calling the procedure just not so much on returning the return value to vb6 and parsing the results. Could you help in that area or is this a new question?
Thanks,
Jerry
I have a pretty good grasp on calling the procedure just not so much on returning the return value to vb6 and parsing the results. Could you help in that area or is this a new question?
Thanks,
Jerry
This should get you started. I wrote it in VBA, but I think it will transfer over to VB6.
You will have to supply the additional parameters using cmd.Parameters.Append cmd.CreateParameter(...
I only passed in the first one.
The code calls the procedure, then parses the xml and prints out the name/value pairs.
You will have to supply the additional parameters using cmd.Parameters.Append cmd.CreateParameter(...
I only passed in the first one.
The code calls the procedure, then parses the xml and prints out the name/value pairs.
Private Sub CommandButton1_Click()
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As ADODB.Command
Dim strconnect As String
Dim xmlresult As String
strconnect = "Provider=SQLOLEDB;Data Source=SALLY\SQLEXPRESS;Initial Catalog=test;Integrated Security=SSPI;"
con.Open strconnect
Set cmd = New ADODB.Command
cmd.ActiveConnection = con
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_CreditSale"
'Add parameters here
cmd.Parameters.Append cmd.CreateParameter("@IpAddress", adVarChar, adParamInput, 100, "192.168.1.1")
Set rs = cmd.Execute
If Not rs.EOF Then
xmlresult = rs.fields(0)
End If
Set cmd.ActiveConnection = Nothing
Call ParseXML(xmlresult)
End Sub
Private Sub ParseXML(xmlIn As String)
Dim objXML As MSXML2.DOMDocument
Dim objNode As MSXML2.IXMLDOMNode
Set objXML = New MSXML2.DOMDocument
If Not objXML.loadXML(xmlIn) Then
Err.Raise objXML.parseError.errorCode, , objXML.parseError.reason
End If
' get the Transaction node
Set objNode = objXML.selectSingleNode("TStream/Transaction")
Call GetElements(objNode)
Set objNode = Nothing
Set objXML = Nothing
End Sub
Private Sub GetElements(node As MSXML2.IXMLDOMNode)
Dim objEl As MSXML2.IXMLDOMElement
' iterate its sub-nodes
For Each objEl In node.childNodes
If objEl.childNodes.Length > 1 Then
Call GetElements(objEl)
Else
Debug.Print objEl.nodeName & " = " & objEl.Text
End If
Next
End Sub
ASKER
Erik37!!!!!!!!!
That looks like it should do it!!!!!!!!!! I'll test today!!
Thanks a bunch for the response!!!
Jerry
That looks like it should do it!!!!!!!!!! I'll test today!!
Thanks a bunch for the response!!!
Jerry
ASKER
Erick37!!!!!
I am getting a properly formated xml statement and when I run it through the program provided by the vendor it returns a successful transaction string. It then replaced the chr(10)'s with vbCrLf's to make the returned string look like XML. I can't make you parsing procedures process this string. I can see the text but it doesn't seem to have any nodes. What is the type of xmlresult? It is giving me a type mismatch which may be the problem. I am Dimming it as a string. I know this is difficult without code to run but I certainly appreciate any help you can give me.
Thank,
Jerry
I am getting a properly formated xml statement and when I run it through the program provided by the vendor it returns a successful transaction string. It then replaced the chr(10)'s with vbCrLf's to make the returned string look like XML. I can't make you parsing procedures process this string. I can see the text but it doesn't seem to have any nodes. What is the type of xmlresult? It is giving me a type mismatch which may be the problem. I am Dimming it as a string. I know this is difficult without code to run but I certainly appreciate any help you can give me.
Thank,
Jerry
ASKER
Erick37!!!!!!!!!!!!!!!
We are almost there!! The attached code is returned from the processor when the stored procedure you helped me write is run. When I run this code through the Parser you sent there is no objNode being sent to the GetElements Sub. I think if we could get this figured out we'd be done!!!
Call GetElements(objNode)
Thanks for all your help!!!!!!!!!!!!!
Jerry
We are almost there!! The attached code is returned from the processor when the stored procedure you helped me write is run. When I run this code through the Parser you sent there is no objNode being sent to the GetElements Sub. I think if we could get this figured out we'd be done!!!
Call GetElements(objNode)
Thanks for all your help!!!!!!!!!!!!!
Jerry
<?xml version="1.0"?>
<RStream>
<CmdResponse>
<ResponseOrigin>Processor</ResponseOrigin>
<DSIXReturnCode>000000</DSIXReturnCode>
<CmdStatus>Approved</CmdStatus>
<TextResponse>AP</TextResponse>
<UserTraceData></UserTraceData>
</CmdResponse>
<TranResponse>
<MerchantID>595901</MerchantID>
<AcctNo>5499990123456781</AcctNo>
<ExpDate>1015</ExpDate>
<CardType>M/C</CardType>
<TranCode>Sale</TranCode>
<AuthCode>000027</AuthCode>
<CaptureStatus>Captured</CaptureStatus>
<RefNo>0209</RefNo>
<InvoiceNo>370021</InvoiceNo>
<OperatorID>test</OperatorID>
<Memo>POSiTrack Software Inc. 3.2.160</Memo>
<Amount>
<Purchase>1.13</Purchase>
<Authorize>1.13</Authorize>
</Amount>
<AcqRefData>KbMCC0330121206 </AcqRefData>
<ProcessData>|00|410100700000</ProcessData>
</TranResponse>
</RStream>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Erick37!!!!!!!!!!!!!!!!!!! !!!!!!!!!! !!!!!!!!!! !!!!!!!!!! !!!!!!!!!! !!!!!!!
I wish there was a higher grade and more points I could give you!!!!!!!!!!
Thanks,
Jerry
I wish there was a higher grade and more points I could give you!!!!!!!!!!
Thanks,
Jerry
Glad it worked out, thanks!
Which do you prefer?