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!

Register Log in
  • 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

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
 
Sort by date Sort by votes
Status
Not open for further replies.

Similar threads

Makumbi256
  • Locked
  • Question
How to make days vertical and grid view editable using asp.net vb
Replies
0
Views
264
Makumbi256
Makumbi256
AhmedAzizBarbary
  • Locked
  • Question
How to read data response body after success login razor page ?
Replies
0
Views
380
AhmedAzizBarbary
AhmedAzizBarbary
AhmedAzizBarbary
  • Locked
  • Question
How to pass list of checkboxes not checked on page model razor page?
Replies
0
Views
414
AhmedAzizBarbary
AhmedAzizBarbary
a6green
  • Locked
  • Question
How to configure AT&T / Avaya Definity G3 incoming ISDN trunk
Replies
2
Views
375
a6green
a6green
Makumbi256
  • Locked
  • Question
how can i convert this code to cshap
Replies
1
Views
334
xwb
xwb

Part and Inventory Search

Sponsor

Back
Top