Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 303
  • Last Modified:

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
<?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>.

Open in new window

0
JDL129
Asked:
JDL129
  • 9
  • 8
1 Solution
 
Erick37Commented:
The XML can be filled from either the UI (VB6) or from SQL.

Which do you prefer?
0
 
YZlatCommented:
use place holders. check this out:

http://forums.asp.net/p/1502077/3555829.aspx
0
 
Erick37Commented:
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.
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'

Open in new window

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
JDL129Author Commented:
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
0
 
Erick37Commented:
How is this xml to be used?  Are you sending it / using it from your VB6 app or from SQL Server?
0
 
JDL129Author Commented:
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
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'

Open in new window

0
 
JDL129Author Commented:
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
0
 
Erick37Commented:
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  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

Open in new window

0
 
JDL129Author Commented:
Erik37!!!!!!
Works like a charm!!!!!  How do I get the results back in vb6 and parse the return XML?

Thanks for the response!!
Jerry
0
 
Erick37Commented:
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

0
 
JDL129Author Commented:
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
0
 
Erick37Commented:
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.
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

Open in new window

0
 
JDL129Author Commented:
Erik37!!!!!!!!!
That looks like it should do it!!!!!!!!!!  I'll test today!!
Thanks a bunch for the response!!!
Jerry
0
 
JDL129Author Commented:
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
0
 
JDL129Author Commented:
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
<?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>

Open in new window

0
 
Erick37Commented:
Tis is looking for all nodes inside TStream/Transaction:
Set objNode = objXML.selectSingleNode("TStream/Transaction")

Since your xml has changed, maybe use this:
Set objNode = objXML.selectSingleNode("RStream")
0
 
JDL129Author Commented:
Erick37!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
I wish there was a higher grade and more points I could give you!!!!!!!!!!

Thanks,
Jerry
0
 
Erick37Commented:
Glad it worked out, thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now