Solved

Need help with XML

Posted on 2010-11-29
18
280 Views
Last Modified: 2012-05-10
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
Comment
Question by:JDL129
  • 9
  • 8
18 Comments
 
LVL 32

Expert Comment

by:Erick37
ID: 34230833
The XML can be filled from either the UI (VB6) or from SQL.

Which do you prefer?
0
 
LVL 35

Expert Comment

by:YZlat
ID: 34231324
use place holders. check this out:

http://forums.asp.net/p/1502077/3555829.aspx
0
 
LVL 32

Expert Comment

by:Erick37
ID: 34231413
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
 

Author Comment

by:JDL129
ID: 34233989
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
 
LVL 32

Expert Comment

by:Erick37
ID: 34234075
How is this xml to be used?  Are you sending it / using it from your VB6 app or from SQL Server?
0
 

Author Comment

by:JDL129
ID: 34234172
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
 

Author Comment

by:JDL129
ID: 34234211
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
 
LVL 32

Expert Comment

by:Erick37
ID: 34238742
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
 

Author Comment

by:JDL129
ID: 34239069
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 32

Expert Comment

by:Erick37
ID: 34239283
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
 

Author Comment

by:JDL129
ID: 34242061
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
 
LVL 32

Expert Comment

by:Erick37
ID: 34243334
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
 

Author Comment

by:JDL129
ID: 34257384
Erik37!!!!!!!!!
That looks like it should do it!!!!!!!!!!  I'll test today!!
Thanks a bunch for the response!!!
Jerry
0
 

Author Comment

by:JDL129
ID: 34270746
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
 

Author Comment

by:JDL129
ID: 34281649
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
 
LVL 32

Accepted Solution

by:
Erick37 earned 500 total points
ID: 34282826
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
 

Author Closing Comment

by:JDL129
ID: 34284785
Erick37!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
I wish there was a higher grade and more points I could give you!!!!!!!!!!

Thanks,
Jerry
0
 
LVL 32

Expert Comment

by:Erick37
ID: 34284800
Glad it worked out, thanks!
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now