Solved

Need help with XML

Posted on 2010-11-29
18
283 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Browsing the questions asked to the Experts of this forum, you will be amazed to see how many times people are headaching about monster regular expressions (regex) to select that specific part of some HTML or XML file they want to extract. The examp…
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 Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

828 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