locked
how to insert XML data into MSSQL table as Row,Column? RRS feed

  • Question

  • User-1640542475 posted

    Hi sir

    I need to insert xml data into mssql table. But I stored Json format into Mssql Table

    This is the Json Code. It is working Fine.

    Private Sub InsertJsonSP()
            'Dim url As String = "https://api.mocki.io/v1/b043df5a"
            Dim url As String = "http://localhost/delivery/JsonData/WebService.asmx/GetAllCustomer"
            ServicePointManager.Expect100Continue = True
            ServicePointManager.SecurityProtocol = CType(3072, SecurityProtocolType)
            Dim json As String = (New WebClient).DownloadString(url.TrimEnd())
            Dim res As List(Of BouncesAndBlocks) = JsonConvert.DeserializeObject(Of List(Of BouncesAndBlocks))(json)
            For Each bouncesAndBlock In res
                Dim cmd As New SqlCommand
                cmd.Connection = conn
                cmd.CommandType = CommandType.StoredProcedure
                cmd.CommandText = "usp_API_Customer_List"
                cmd.Parameters.Add(New SqlParameter("@Action", "INSERT"))
    
                cmd.Parameters.AddWithValue("@code", bouncesAndBlock.code)
                cmd.Parameters.AddWithValue("@Name", bouncesAndBlock.name)
                cmd.Parameters.AddWithValue("@type", bouncesAndBlock.type)
                cmd.Parameters.AddWithValue("@transporter", bouncesAndBlock.transporter)
    
                Dim da As New SqlDataAdapter(cmd)
                Dim dt As New DataTable
                da.Fill(dt)
    
                cmd.Parameters.Clear()
                cmd.Dispose()
            Next
        End Sub
    
        Public Class BouncesAndBlocks
            Public Property code As String
            Public Property name As String
            Public Property type As String
            Public Property transporter As String
        End Class

    But I need the xml to be stored as like as Json

    Please Advice me

    Thank you

    Maideen

    Tuesday, April 6, 2021 4:56 AM

Answers

  • User-939850651 posted

    Hi maideen5,

    I have used and changed accordingly. I debugged and error is "System.Xml.XmlException: 'Data at the root level is invalid"

    We cannot test your current code, so we cannot determine the cause of your current problem.

    But before you want to use the data, you should get the data based on the result node and convert it into a collection of objects.

    Something like this:

    Private Shared Sub Main(ByVal args As String())
            Dim xml As String = "<Result>
                                  <BouncesAndBlocks>
                                    <code>Code1</code>
                                    <Name>Name1</Name>
                                    <type>Type1</type>
                                    <transporter>Transporter1</transporter>
                                  </BouncesAndBlocks>
                                  <BouncesAndBlocks>
                                    <code>Code2</code>
                                    <Name>Name2</Name>
                                    <type>Type2</type>
                                    <transporter>Transporter2</transporter>
                                  </BouncesAndBlocks>
                                  <BouncesAndBlocks>
                                    <code>Code3</code>
                                    <Name>Name3</Name>
                                    <type>Type3</type>
                                    <transporter>Transporter3</transporter>
                                  </BouncesAndBlocks>
                            </Result>"
            Dim doc As XmlDocument = New XmlDocument()
            doc.LoadXml(xml)
            Dim jsonText As String = JsonConvert.SerializeXmlNode(doc)
            Dim o As JObject = JObject.Parse(jsonText)
            Dim a As JArray = CType(o("Result")("BouncesAndBlocks"), JArray)
            Dim list As List(Of BouncesAndBlocks) = CType(a.ToObject(Of IList(Of BouncesAndBlocks))(), List(Of BouncesAndBlocks))
            list.ForEach(Function(i) Console.WriteLine(i.code & vbTab + i.name & vbTab + i.type & vbTab + i.transporter))
            Console.ReadLine()
        End Sub
    
        Public Class BouncesAndBlocks
            Public Property code As String
            Public Property name As String
            Public Property type As String
            Public Property transporter As String
        End Class

    Result:

    You need to make corresponding modifications according to the results (xml structure) you get.

    Best regards,

    Xudong Peng

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 8, 2021 5:29 AM

All replies

  • User-939850651 posted

    Hi maideen5,

    But I need the xml to be stored as like as Json

    According to your description, will you get the result in xml format from WebService? I think what you are looking for should be to convert xml data to json data.

    If this is the case, I recommand that you could use the JsonConvert class which contains helper methods for this precise purpose:

    // To convert an XML node contained in string xml into a JSON string   
    XmlDocument doc = new XmlDocument();
    doc.LoadXml(xml);
    string jsonText = JsonConvert.SerializeXmlNode(doc);
    
    // To convert JSON text contained in string json into an XML node
    XmlDocument doc = JsonConvert.DeserializeXmlNode(json);

    You could read related document for more details:newtonsoft Converting between JSON and XML

    Best regards,

    Xudong Peng

    Wednesday, April 7, 2021 6:44 AM
  • User-1640542475 posted

    Hi XuDong

    I have used and changed accordingly. I debugged and error is "System.Xml.XmlException: 'Data at the root level is invalid"

    Below is my code

    Pls advice me

    Dim url As String = "http://xxx.xxx.xxx.xxx/delivery/JsonData/WebService.asmx/GetAllCustomerXML"
    
            Dim doc As XmlDocument = New XmlDocument()
            doc.LoadXml(url)
            Dim jsonText As String = JsonConvert.SerializeXmlNode(doc)
    
            ServicePointManager.Expect100Continue = True
            ServicePointManager.SecurityProtocol = CType(3072, SecurityProtocolType)
    
            Dim json As String = (New WebClient).DownloadString(jsonText)
            Dim res As List(Of BouncesAndBlocks) = JsonConvert.DeserializeObject(Of List(Of BouncesAndBlocks))(json)
            For Each bouncesAndBlock In res
                Dim cmd As New SqlCommand
                cmd.Connection = conn
                cmd.CommandType = CommandType.StoredProcedure
                cmd.CommandText = "usp_API_Customer_List"
                cmd.Parameters.Add(New SqlParameter("@Action", "INSERT"))
    
                cmd.Parameters.AddWithValue("@code", bouncesAndBlock.code)
                cmd.Parameters.AddWithValue("@Name", bouncesAndBlock.name)
                cmd.Parameters.AddWithValue("@type", bouncesAndBlock.type)
                cmd.Parameters.AddWithValue("@transporter", bouncesAndBlock.transporter)
    
                Dim da As New SqlDataAdapter(cmd)
                Dim dt As New DataTable
                da.Fill(dt)
    
                cmd.Parameters.Clear()
                cmd.Dispose()
            Next

    Thursday, April 8, 2021 1:34 AM
  • User-939850651 posted

    Hi maideen5,

    I have used and changed accordingly. I debugged and error is "System.Xml.XmlException: 'Data at the root level is invalid"

    We cannot test your current code, so we cannot determine the cause of your current problem.

    But before you want to use the data, you should get the data based on the result node and convert it into a collection of objects.

    Something like this:

    Private Shared Sub Main(ByVal args As String())
            Dim xml As String = "<Result>
                                  <BouncesAndBlocks>
                                    <code>Code1</code>
                                    <Name>Name1</Name>
                                    <type>Type1</type>
                                    <transporter>Transporter1</transporter>
                                  </BouncesAndBlocks>
                                  <BouncesAndBlocks>
                                    <code>Code2</code>
                                    <Name>Name2</Name>
                                    <type>Type2</type>
                                    <transporter>Transporter2</transporter>
                                  </BouncesAndBlocks>
                                  <BouncesAndBlocks>
                                    <code>Code3</code>
                                    <Name>Name3</Name>
                                    <type>Type3</type>
                                    <transporter>Transporter3</transporter>
                                  </BouncesAndBlocks>
                            </Result>"
            Dim doc As XmlDocument = New XmlDocument()
            doc.LoadXml(xml)
            Dim jsonText As String = JsonConvert.SerializeXmlNode(doc)
            Dim o As JObject = JObject.Parse(jsonText)
            Dim a As JArray = CType(o("Result")("BouncesAndBlocks"), JArray)
            Dim list As List(Of BouncesAndBlocks) = CType(a.ToObject(Of IList(Of BouncesAndBlocks))(), List(Of BouncesAndBlocks))
            list.ForEach(Function(i) Console.WriteLine(i.code & vbTab + i.name & vbTab + i.type & vbTab + i.transporter))
            Console.ReadLine()
        End Sub
    
        Public Class BouncesAndBlocks
            Public Property code As String
            Public Property name As String
            Public Property type As String
            Public Property transporter As String
        End Class

    Result:

    You need to make corresponding modifications according to the results (xml structure) you get.

    Best regards,

    Xudong Peng

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 8, 2021 5:29 AM