(How to)Read XML file using XDocument and build dynamic SQL query?

See more:

VB

XML

Hi, Here I wrote a function ReadXML().This function reads XML file and returns a string.The string contains all the XML file nodes values to build SQL query dynamically. Any way My code is working. Is this right way? Can you please suggest me the unnecessary code(loops...) in this function, So that I can do the code in a better way.

Here is the function.

Public Function ReadXML(ByVal tblName As String) As String

        Dim strBuil As New StringBuilder
        Dim listSource As New List(Of String)
        Dim listTarget As New List(Of String)
        Dim dictionary As New Dictionary(Of String, String)
        Dim xmlDoc As XDocument = XDocument.Load("C:\\MappingFile.xml")

        Dim q = (From c In xmlDoc.Descendants("Entity") Where c.Attribute("Source").Value = tblName
                 Select New With {
                     .EntityTarget = c.Attribute("Target").Value,
                     .PropertySource = c.Elements("Property").Attributes("Source"),
                     .PropertyTarget = c.Elements("Property").Attributes("Target")
                     })


        For Each itm In q

            Dim entitytarget As String = itm.EntityTarget



            For Each propertysrce In itm.PropertySource
Dim prpsource As String = propertysrce.ToString().Remove(0, 8) //Here propertytrgt value is like Source="...". So I am removing unnecessary part and adding value to list.


                prpsource = prpsource.Remove(prpsource.Length - 1)

                listSource.Add(prpsource)
            Next
            listSource.Add(entitytarget)
For Each propertytrgt In itm.PropertyTarget  //Here propertytrgt value is like Target="...". So I am removing unnecessary part and adding value to list.

                Dim prptarget As String = propertytrgt.ToString().Remove(0, 8)
                prptarget = prptarget.Remove(prptarget.Length - 1)
                listTarget.Add(prptarget)
            Next
            listTarget.Add(entitytarget)
        Next


    // HERE adding two lists(listTarget  and  listSource) to Dictionary

        For Each sourceValue In listSource
            Dim Source As String = sourceValue
            Dim count As Int32 = listTarget.Count
            If I <> count Then
                Dim Target As String = listTarget.Item(I)
                dictionary.Add(Source, Target)
                I = I + 1
            End If

        Next

       '===============STRING BUILDER


        strBuil.Append("select ")

        For Each itm In dictionary
            If n <= dictionary.Count - 2 Then
                strBuil.Append(itm.Value.ToString + " " + "as " + itm.Key.ToString + ",")
                n = n + 1
            ElseIf n = dictionary.Count - 1 Then

                strBuil = strBuil.Remove(strBuil.Length - 1, 1)

                strBuil.Append(" from " + itm.Value.ToString)
            Else
                Exit For
            End If
        Next


        Return strBuil.ToString()

    End Function

Here is the XML file.

<?xml version="1.0" encoding="utf-8" ?>
<Entities>

  <Entity Source="E_cdclient" Target="cd_client">
    <Property Source="Name" Target="client_name"/>
    <Property Source="Client_ShortDesc" Target="client_name_short"/>
    <Property Source="PeriodStart" Target="client_period_start"/>
    <Property Source="PeriodEnd" Target="client_period_end"/>
    <Property Source="Comments" Target="client_remark"/>
  </Entity>
  <Entity Source="E_cdclient_cdclientcontact" Target="cd_client_contact">
    <Property Source="Surname" Target="Surname"/>
    <Property Source="Familyname" Target="Familyname"/>
    <Property Source="Phone" Target="Phone"/>
    <Property Source="EMail" Target="EMail"/>
    <Property Source="Street" Target="Street"/>
    <Property Source="City" Target="City"/>
    <Property Source="ZIP" Target="ZIP"/>
    <Property Source="Responsibility" Target="Responsibility"/>
  </Entity>
</Entities>

Thanks & Regards,
JN