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 SkipVought 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
0
0
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

AhmedAzizBarbary
  • Locked
  • Question
How to read data response body after success login razor page ?
Replies
0
Views
154
AhmedAzizBarbary
AhmedAzizBarbary
AhmedAzizBarbary
  • Locked
  • Question
How to pass list of checkboxes not checked on page model razor page?
Replies
0
Views
151
AhmedAzizBarbary
AhmedAzizBarbary
a6green
  • Locked
  • Question
How to configure AT&T / Avaya Definity G3 incoming ISDN trunk
Replies
2
Views
166
a6green
a6green
Makumbi256
  • Locked
  • Question
how can i convert this code to cshap
Replies
1
Views
131
xwb
xwb
sugu1
  • Locked
  • Question
sound editing in web site to record, cut/trim, paste and save as wav file
Replies
0
Views
129
sugu1
sugu1

Part and Inventory Search

Sponsor

Back
Top