Link to home
Start Free TrialLog in
Avatar of JDL129
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
<?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

Avatar of Erick37
Erick37
Flag of United States of America image

The XML can be filled from either the UI (VB6) or from SQL.

Which do you prefer?
Avatar of YZlat
use place holders. check this out:

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

Avatar of JDL129
JDL129

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
How is this xml to be used?  Are you sending it / using it from your VB6 app or from SQL Server?
Avatar of JDL129

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

Avatar of JDL129

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

Avatar of JDL129

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

Avatar of JDL129

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

Avatar of JDL129

ASKER

Erik37!!!!!!!!!
That looks like it should do it!!!!!!!!!!  I'll test today!!
Thanks a bunch for the response!!!
Jerry
Avatar of JDL129

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
Avatar of JDL129

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

ASKER CERTIFIED SOLUTION
Avatar of Erick37
Erick37
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JDL129

ASKER

Erick37!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
I wish there was a higher grade and more points I could give you!!!!!!!!!!

Thanks,
Jerry
Glad it worked out, thanks!