GetXML from DataTable Specify Option Serialize Column Content Like CData

Question

I have and datable and a column of string data type in that column i put content of type ddl some like

create table
create view

create stored procedure

dt.columns("content") = "create procedure ...etc"

that work ok,

the problem is when i need recovery the content like xml. Now i do that with

Dim xmlcontent = dt.DataSet.GetXml().ToString()

that return a xml like

<NewDataSet>
 <Application_table>
 <ID>PV</ID>
 <NAme>dbo.uvw_Sample</Nombre>
 <Content>/*
Run this script on SQL Server 2008 or later. There may be flaws if running on earlier versions of SQL Server.
*/
 Create View...
...
 </Content>
 </Application_table>
</NewDataSet>

the exception is

XML parsing: line 5349, character 59, illegal xml character

i think the content in column content should for example change "<" for "<" or better specifiy the content like cdata

So I want to specify that the serialization of the column be as CDATA. Anyone know if there is way to do it?

score 0 · Answer 1 · edited May 23 '17 at 12:17

Use serialization:

private string DataSetToXml(DataSet ds)
        {
            using (var ms= new MemoryStream())
            {
                using (TextWriter sw= new StreamWriter(ms))
                {
                    var xmlSerializer = new XmlSerializer(typeof(DataSet));
                    xmlSerializer.Serialize(sw, ds);
                    return Encoding.UTF8.GetString(memoryStream.ToArray());
                }
            }
        }

or you can use .getXml() returned xml se how you can serialize into string Using StringWriter for XML Serialization

score 0 · Accepted Answer · answered Jul 07 '16 at 13:11

You can try converting the nodes in question to a cdata node. This code might be obsolete as its very old, but it still works. I made an xml document from the dataset. Changed all the text nodes to CDATA nodes. Here is the code:

 workingds = DataSetWithData
'Convert dataset to xmldatadocument
Dim myxmldoc As Xml.XmlDataDocument = New Xml.XmlDataDocument(workingds)

    txtnodes = myxmldoc.GetElementsByTagName(strTextField)
    For i = 0 To txtnodes.Count - 1
        Me.makecdata(txtnodes(i))
    Next i
'this subroutine takes the text node and wraps a cdata tag around it 
'and returns it to the document
Public Sub makecdata(ByVal mynode As Xml.XmlNode)
    Dim mcnode As Xml.XmlCDataSection, strtemp As String
    strtemp = mynode.InnerText

    mcnode = (mynode.OwnerDocument.CreateCDataSection(strtemp))

    mynode.InnerXml = ""
    mynode.AppendChild(mcnode)

    mcnode = Nothing

End Sub

thanks Haim Katz, i resolved that after of get string do and loop and replace nodes content by , your proposal look better — Carlos Cocom, Aug 14 '16 at 06:11

GetXML from DataTable Specify Option Serialize Column Content Like CData

2 Answers2