I have the following code which is importing data from XML fine but only for the parent node.
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
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
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