xml - How do I display sql statement? -

i have function using id_judge xml document , use id_judge create sql select statement. statement string in stroutputsql variable.

every sql select statement same. difference codeid because variable id_judge.

right function's return stroutputsql showing "09002p" . modify variable instead show sql statement have pasted below under header expected sql output.

how do inside function?

for xml document have, function should return following output inside string variable stroutputsql

expected sql output

select *  ucode uc inner join ujudoffc uj on (uj.judicialoffcodeid = uc.codeid) code='09002p' , uc.cachetableid = 136 xml path('judgecode') 

my vb.net 4.0 code

private class messageprocessor     inherits msc.integration.messagebroker.library.v4.xmlmessageprocessor      protected overrides sub processmessage(byref aobjbroker messagebroker.library.v4.broker, byref aobjxmlinputdoc system.xml.xmldocument, byref aobjinstantiatedobjectscollection microsoft.visualbasic.collection)     mybase.processmessage(aobjbroker, aobjxmlinputdoc, aobjinstantiatedobjectscollection)     dim objxmlloadedelement xmlelement     dim strsql string     dim strjudgeid string      objxmlloadedelement = aobjxmlinputdoc.createelement("loaded")     aobjxmlinputdoc.documentelement.appendchild(objxmlloadedelement)      strjudgeid = aobjxmlinputdoc.documentelement.selectsinglenode("id_judge").innertext     strsql = getsql(strjudgeid)     end sub      function getsql(byval astrjudgeid string) string     dim stroutputsql string      stroutputsql = astrjudgeid      return stroutputsql     end function end class 

my xml document id_judge "09002p" used create sql statement in vb.net function.

<?xml version="1.0" encoding="utf-8"?> <gjudge triggerevent="update" xmlns="">    <id_judge>09002p</id_judge>     <loaded>         <judgecode>             <codeid>7598</codeid>             <cachetableid>136</cachetableid>             <revisionid>5321</revisionid>             <code>07001g</code>             <rootnodeid>0</rootnodeid>             <effectivedate>2007-06-14t00:00:00</effectivedate>             <useridcreate>1</useridcreate>             <timestampcreate>2003-02-01t16:45:00</timestampcreate>             <useridchange>1</useridchange>             <timestampchange>2015-07-06t09:49:27.700</timestampchange>             <description>amos,wako ,</description>             <judicialoffcodeid>7598</judicialoffcodeid>             <namefirst>amos</namefirst>             <namelast>wako</namelast>             <unavailableonly>1</unavailableonly>             <private>0</private>         </judgecode>     </loaded> </gjudge> 

you have careful using basic string concatenation when assembling sql statements, particularly when don't control source. unsafe , opens potential sql injections.

the more appropriate way use parameters command object. see microsoft article example. sql update principle still works select.

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters(v=vs.110).aspx?cs-save-lang=1&cs-lang=vb#code-snippet-2