Dot Net XML node Un-Escape double quotes

134 Views Asked by At

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&quot;" 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.

0

There are 0 best solutions below