Data set with xml element null(varchar) value
-
I have a dataset that holds data that was retrieved from a database. Some of the data in the dataset is null (for some, null for the whole column). Upon successful retrieval of data, I want to write the data to an XML file.
Problem: When I use the dataset.WriteXML(filename) method, it successfully writes the data to the xml file, BUT the columns that have null values throughout get left out of the final output (if there is a field that has a null value then it will not be represented in that record in the xml file).
Question: Is there any way I can force the writing of the entire dataset (All columns being represented regardless if an entire column is null)?
Thanks in advance for your help.
-
Hi there,
Dim xs As New System.Xml.XmlWriterSettings
xs.Indent = True
Dim xw As System.Xml.XmlWriter = System.Xml.XmlWriter.Create("C:\Output.xml", xs)
xw.WriteStartElement("SearchResult")
Dim sqldb As New SqlDataProvider.SqlDatabase(Common.LavanConfig.SqlExpressConnectionString)
Dim dr As IDataReader = sqldb.ExecuteReader("dbo.cs_Warehouse_FindByBarcode", Nothing, "6261216001081")
While dr.Read()
xw.WriteElementString("ProductModel", dr("ProductModel"))
xw.WriteElementString("Price", IIf(IsDBNull(dr("Price")), "", dr("Price")))
xw.WriteElementString("Discount", dr("Discount"))
xw.WriteElementString("Tax", dr("Tax"))
xw.WriteElementString("WarrantyDuration", dr("WarrantyDuration"))
End While
dr.Close()
xw.WriteEndElement()
xw.Flush()
xw.Close() -
Thanks for your time.
I am using below code to get xml file from dataset .
where i want to write code
If any data colum value is null then I need output is with empty xml element.
Code:
Public Shared Sub writeSQLQueryToFileAsXML(ByVal query As String, ByVal filename As String)
Dim myConn As New SqlConnection(getConnectionString)
Dim adapter As New SqlDataAdapter adapter.SelectCommand = New SqlCommand(query, myConn)
Dim myDs As New DataSet
adapter.Fill(myDs)
Dim myFs As IO.FileStream = Nothing
'Get a FileStream object
myFs = New IO.FileStream(filename, IO.FileMode.Create) ', IO.FileAccess.Write)
' Apply the WriteXml method to write an XML document
Dim myXmlWriter As New System.Xml.XmlTextWriter _ (myFs, System.Text.Encoding.Unicode)
myDs.DataSetName = "SamoleData"
myDs.Tables(0).TableName = "FormInfo"
myXmlWriter.WriteStartDocument()
myDs.WriteXml(myXmlWriter, XmlWriteMode.WriteSchema)
myXmlWriter.Close()
End Sub
Thanks in advance.
-
You can use DataReader instead of DataAdapter.
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Me.Load
WriteSQLQueryToFileAsXML("Select * From dbo.Users", "C:\Users.xml")
End Sub
Public Shared Sub WriteSQLQueryToFileAsXML(ByVal query As String, ByVal filename As String)
Dim xs As New System.Xml.XmlWriterSettings
xs.Indent = True
Dim xw As System.Xml.XmlWriter = System.Xml.XmlWriter.Create(filename, xs)
xw.WriteStartElement("QueryResult")
Dim SqlConn As New SqlConnection(getConnectionString)
Dim SqlComm As New SqlCommand(query, SqlConn)
SqlConn.Open()
Dim dr As SqlDataReader = SqlComm.ExecuteReader(CommandBehavior.CloseConnection)
While dr.Read()
xw.WriteStartElement("Record")
For i As Integer = 0 To dr.FieldCount - 1
xw.WriteElementString(dr.GetName(i), IIf(IsDBNull(dr.GetValue(i)), String.Empty, dr.GetValue(i)))
Next
xw.WriteEndElement()
End While
dr.Close()
xw.WriteEndElement()
xw.Flush()
xw.Close()
End SubHope this helps
-
Thank you for your help.It was worked perfectly.
-
when i used the code
the xml looks like below
- <FormData>
- <FormInfo>
<DATE>071021100003</DATE>
<ID>1000000030</ID>
<CMNTH>9</CMNTH>
<CDT>14</CDT>
<CYR>2006</CYR>
<TEST>8</TEST><DT_TEST>15</DT_TEST>
<YR_TEST>2006</YR_TEST>
<EVER_TEST>1</EVER_TEST>
<LST_TEST>8</LST_TEST>
<DT_OF_LST_HIV_TEST>15</DT_OF_LST_HIV_TEST>
<YR_HTEST>2005</YR_HTEST>
<NMBR>2</NMBR>
<NMBR_YRS>3</NMBR_YRS>
<MDN>1</MDN>
<CD1>01</CD1>
<CD2>02</CD2>
<CD3>03</CD3>
<CD4>04</CD4>
<FRST_MD>69</FRST_MD>
<DT_OF_MD>18</DT_OF_MD>
<FR>2007</FR>
<LSTMDC>10</LSTMDC>
<LSTTN>11</LSTTN>
<LSTMDCT>2007</LSTMDCT>
</FormInfo>
</FormData>But i am looking the out put below one.How to write
- <FormData>
- <FormInfo>
<DATE>071021100003</DATE>
<ID>1000000030</ID>
<CMNTH>9</CMNTH>
<CDT>14</CDT>
<CYR>2006</CYR>
<TEST>8</TEST>
</FormInfo>
-<Date>
<DT_TEST>15</DT_TEST>
<YR_TEST>2006</YR_TEST>
<EVER_TEST>1</EVER_TEST>
<LST_TEST>8</LST_TEST>
<DT_OF_LST_HIV_TEST>15</DT_OF_LST_HIV_TEST>
<YR_HTEST>2005</YR_HTEST>
</Date>
-<NumberCode>
<NMBR>2</NMBR>
<NMBR_YRS>3</NMBR_YRS>
<MDN>1</MDN>
<CD1>01</CD1>
<CD2>02</CD2>
<CD3>03</CD3>
<CD4>04</CD4>
</NumberCode>
-<MDC>
<FRST_MD>69</FRST_MD>
<DT_OF_MD>18</DT_OF_MD>
<FR>2007</FR>
<LSTMDC>10</LSTMDC>
<LSTTN>11</LSTTN>
<LSTMDCT>2007</LSTMDCT>
</MDC
</FormData> -
Thanks for your time.
Now my req is changed
If any data colum value is null then I dont need output of the xml element.
Please suggest .
Thanks in advance.
-
Hi there,
Check it out :
For i As Integer = 0 To dr.FieldCount - 1
If (Not dr.IsDBNull(i)) Then
xw.WriteElementString(dr.GetName(i), dr.GetValue(i).ToString())
Else
xw.WriteStartElement(dr.GetName(i))
xw.WriteEndElement()
End If
Next -
Thanks for your time ..
My req is not like this .Your posted code is for data value is null it showing particular null elemet.
Now i wanted to get in xml file if data value is null i dont want to show that end element
Post a reply
XML forum discussion
-
XBrowse
by liuying9104 (3 replies)
-
generating xml file
by huangdm05941 (2 replies)
-
urgent
by huangdm05941 (2 replies)
-
How to increment variable value in xslt
by huangdm05941 (1 replies)
-
Inside Open XML
by jettimadhu (2 replies)
Quick links
Recent activity
- arif ahmad replied to How to receive data in web ...
- William Thompson replied to What is the name of the Win...
- Sameera Piyadigamage replied to Point of Sale Developers: H...
- Scott Carline replied to 4 x C# Developers for large...
- Rajendra Dhakal replied to Restore SQL Server text dat...
- cloud rainda replied to How to convert between TS f...
Enter your message below
Sign in or Join us (it's free).