Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Reading XML file to SQL Server

Status
Not open for further replies.

primagic

IS-IT--Management
Jul 24, 2008
476
GB
I have the following code which is importing data from XML fine but only for the parent node.

Code:
Dim Mapping1 As New SqlBulkCopyColumnMapping("submission-reference", "SubmissionRef")
        Dim Mapping2 As New SqlBulkCopyColumnMapping("created-at", "DateCreated")
        Dim Mapping3 As New SqlBulkCopyColumnMapping("body", "Question")


        Dim reportData As DataSet = New DataSet
        reportData.ReadXml(Server.MapPath("2014-06-06_0000_BCW_TCF_Survey_Requests_Report.xml"))
        Dim ConnString As [String] = ConfigurationManager.ConnectionStrings("TCFConnectionString").ConnectionString
        Dim con As New SqlConnection(ConnString)


        Dim sbc As SqlBulkCopy = New SqlBulkCopy(con)

        sbc.ColumnMappings.Add(Mapping1)
        sbc.ColumnMappings.Add(Mapping2)
        sbc.ColumnMappings.Add(Mapping3)
        sbc.DestinationTableName = "Responses"
        con.Open()
        sbc.WriteToServer(reportData.Tables(0))

If I try to add a mapping to a child node then I get an error that column mapping cant be found in datasource.

Here is the xml file

Code:
<submissions>
  <submission>
    <survey-id>1</survey-id>
    <survey-name>csat1</survey-name>
    <brand>bcw_survey</brand>
    <source>mobile-web</source>
    <questions>3</questions>
    <answers>3</answers>
    <created-at>2014-06-05T09:36:39+01:00</created-at>
    <survey-submission-results>
      <survey-submission-result>
        <question-id>1</question-id>
        <body>Do you feel your agent understood your circumstances and was listening to all that was said?</body>
        <answer-type>boolean</answer-type>
        <answer>true</answer>
      </survey-submission-result>
      <survey-submission-result>
        <question-id>2</question-id>
        <body>Was the best outcome reached for you by the end of the call?</body>
        <answer-type>boolean</answer-type>
        <answer>true</answer>
      </survey-submission-result>
      <survey-submission-result>
        <question-id>3</question-id>
        <body>Do you fully understand what will happen next and what you need to do?</body>
        <answer-type>boolean</answer-type>
        <answer>true</answer>
      </survey-submission-result>
    </survey-submission-results>
    <client-reference>23184310350710</client-reference>
    <submission-reference>620015271335</submission-reference>
    <agent-reference>357493780718</agent-reference>
  </submission>
  <submission>
    <survey-id>1</survey-id>
    <survey-name>csat1</survey-name>
    <brand>bcw_survey</brand>
    <source>mobile-web</source>
    <questions>3</questions>
    <answers>3</answers>
    <created-at>2014-06-05T16:38:06+01:00</created-at>
    <survey-submission-results>
      <survey-submission-result>
        <question-id>1</question-id>
        <body>Do you feel your agent understood your circumstances and was listening to all that was said?</body>
        <answer-type>boolean</answer-type>
        <answer>true</answer>
      </survey-submission-result>
      <survey-submission-result>
        <question-id>2</question-id>
        <body>Was the best outcome reached for you by the end of the call?</body>
        <answer-type>boolean</answer-type>
        <answer>true</answer>
      </survey-submission-result>
      <survey-submission-result>
        <question-id>3</question-id>
        <body>Do you fully understand what will happen next and what you need to do?</body>
        <answer-type>boolean</answer-type>
        <answer>true</answer>
      </survey-submission-result>
    </survey-submission-results>
    <client-reference>0225001387971</client-reference>
    <submission-reference>025215351180</submission-reference>
    <agent-reference>511867256186</agent-reference>
  </submission>
  <submission>
    <survey-id>1</survey-id>
    <survey-name>csat1</survey-name>
    <brand>bcw_survey</brand>
    <source>mobile-web</source>
    <questions>3</questions>
    <answers>3</answers>
    <created-at>2014-06-05T19:06:25+01:00</created-at>
    <survey-submission-results>
      <survey-submission-result>
        <question-id>1</question-id>
        <body>Do you feel your agent understood your circumstances and was listening to all that was said?</body>
        <answer-type>boolean</answer-type>
        <answer>true</answer>
      </survey-submission-result>
      <survey-submission-result>
        <question-id>2</question-id>
        <body>Was the best outcome reached for you by the end of the call?</body>
        <answer-type>boolean</answer-type>
        <answer>true</answer>
      </survey-submission-result>
      <survey-submission-result>
        <question-id>3</question-id>
        <body>Do you fully understand what will happen next and what you need to do?</body>
        <answer-type>boolean</answer-type>
        <answer>true</answer>
      </survey-submission-result>
    </survey-submission-results>
    <client-reference>23033818358966</client-reference>
    <submission-reference>079917228748</submission-reference>
    <agent-reference>162416677251</agent-reference>
  </submission>
  <submission>
    <survey-id>1</survey-id>
    <survey-name>csat1</survey-name>
    <brand>bcw_survey</brand>
    <source>mobile-web</source>
    <questions>3</questions>
    <answers>3</answers>
    <created-at>2014-06-05T20:03:38+01:00</created-at>
    <survey-submission-results>
      <survey-submission-result>
        <question-id>1</question-id>
        <body>Do you feel your agent understood your circumstances and was listening to all that was said?</body>
        <answer-type>boolean</answer-type>
        <answer>true</answer>
      </survey-submission-result>
      <survey-submission-result>
        <question-id>2</question-id>
        <body>Was the best outcome reached for you by the end of the call?</body>
        <answer-type>boolean</answer-type>
        <answer>true</answer>
      </survey-submission-result>
      <survey-submission-result>
        <question-id>3</question-id>
        <body>Do you fully understand what will happen next and what you need to do?</body>
        <answer-type>boolean</answer-type>
        <answer>true</answer>
      </survey-submission-result>
    </survey-submission-results>
    <client-reference>23033810784508</client-reference>
    <submission-reference>949134094005</submission-reference>
    <agent-reference>162416677251</agent-reference>
  </submission>
  <submission>
    <survey-id>1</survey-id>
    <survey-name>csat1</survey-name>
    <brand>bcw_survey</brand>
    <source>mobile-web</source>
    <questions>3</questions>
    <answers>3</answers>
    <created-at>2014-06-05T23:10:07+01:00</created-at>
    <survey-submission-results>
      <survey-submission-result>
        <question-id>1</question-id>
        <body>Do you feel your agent understood your circumstances and was listening to all that was said?</body>
        <answer-type>boolean</answer-type>
        <answer>true</answer>
      </survey-submission-result>
      <survey-submission-result>
        <question-id>2</question-id>
        <body>Was the best outcome reached for you by the end of the call?</body>
        <answer-type>boolean</answer-type>
        <answer>true</answer>
      </survey-submission-result>
      <survey-submission-result>
        <question-id>3</question-id>
        <body>Do you fully understand what will happen next and what you need to do?</body>
        <answer-type>boolean</answer-type>
        <answer>true</answer>
      </survey-submission-result>
    </survey-submission-results>
    <client-reference>5082857901821</client-reference>
    <submission-reference>938307673910</submission-reference>
    <agent-reference>995721442176</agent-reference>
  </submission>
</submissions>

So I need to get the body tag, answer tag from the survey-submission result node

I can get the submission-reference and created-at
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top