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

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

Answer 1

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

Answer 2

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

Answer 3

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