I have a WinForms application in Dot Net framework 4.5 which generates output from SQL Server data into an XML file which is sent to a Third-party. I am getting the outputs correctly for 99% of the cases but have an issue whenever a description field contains special characters like Single Quote(') or Double Quote (") or Ampersand(&) in the description text. These characters get correctly escaped but the Third-party wants this field Description text as Un-escaped and I have spent a lot of time trying to figure out how to do it.
Here is my code:
Dim xmlLineItems As New XmlDocument
Dim elements As IEnumerable(Of XElement)
Dim LineItemCnt As Integer
Using conn As New SqlConnection(My.Settings.myconstr)
conn.Open()
Dim SQL1 As String = String.Empty
SQL1 = "SELECT SrNo as 'Number',PartsDesc as 'Description' ,QtyApproved as 'Quantity', UOMCode as 'MeasureUOM', (SELECT DeptName as '@DepartmentType',CatDesc as '@Name',ModelDesc as '@ModelNumber',LineItem.DwnNo as '@DrawingNumber' FROM MyViewMaster where MasterID = LineItem.MasterID FOR XML path ('Section'), Type) FROM MyViewDetails LineItem WHERE LineItem.MasterID = 4567809 AND StatusID <> 3 ORDER BY SrNo FOR XML AUTO, type"
Dim cmd1 As New SqlXmlCommand(My.Settings.myconstr)
cmd1.RootTag = "Data"
cmd1.CommandText = SQL1
Dim xreader As System.Xml.XmlReader = cmd1.ExecuteXmlReader
xmlLineItems.Load(xreader)
conn.Close()
End Using
Dim lists As XmlNodeList = xmlLineItems.SelectNodes(".//LineItem")
elements = lists.Cast(Of XmlNode)().[Select](Function(node) XDocument.Parse(node.OuterXml).Root)
This generates a list of items and a sample of the LineItem output is as below:
<LineItem Number="1"
. . .
</LineItem>
<LineItem Number="19" Description="DETTOL WASH 5 LTR" Quantity="1" MeasureUOM="PCE">
<Section DepartmentType="HouseKeeping" Name="Consumables" ModelNumber="" DrawingNumber="" />
</LineItem>
<LineItem Number="20" Description="HAND SHOWER CHROMED, 1/2"" Quantity="3" MeasureUOM="PCE">
<Section DepartmentType="HouseKeeping" Name="Accessories" ModelNumber="" DrawingNumber="" />
</LineItem>
<LineItem Number="120"
. . . . .
</LineItem>
I need to have the Description as "HAND SHOWER CHROMED, 1/2"" (Un-EScaped) only for this field.
How do I go about achieving this?
Some additional information: This field is marked as CDATA in the DTD by the 3rd Party to whom we need to send this XML file.