Click here to Skip to main content
15,885,366 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a XML file in my local machine. I want to insert that file as .xml file into a single column of a SQL table. I tried searching internet and don't find any proper answer. I got some answers which explains how to insert the records as a single row but I don't want to split the record and I just want to insert that file as .xml.

XML file:

XML
<IVRDATA>
  <IVR>
    <CALLID>84CAB60BABB711E4BDB9C7154A250D84</CALLID>
    <ICM_CALLID>00-00-00</ICM_CALLID>
    <SESSIONID>10.195.188.78.1423062943712.293.HFS_DCSS_CVP</SESSIONID>
    <APP_ID>3</APP_ID>
    <APP_TYPE>DCSS</APP_TYPE>
    <VXMLSERVER>10.195.188.78</VXMLSERVER>
    <DNIS>8523</DNIS>
    <CLI>914461084538</CLI>
    <CALL_STARTDATETIME>04/02/2015 09:15:43</CALL_STARTDATETIME>
    <CALL_ENDDATETIME>04/02/2015 09:16:23</CALL_ENDDATETIME>
    <LANGCODE>en-us</LANGCODE>
    <SKILLNAME>HFS.DCSS.CSCC.en-us.SG</SKILLNAME>
    <AGENT_TRANSFER>N</AGENT_TRANSFER>
    <ENDTYPE>CD</ENDTYPE>
    <DISPOSITION>CD</DISPOSITION>
    <HOW_CALL_ENDED>HANGUP</HOW_CALL_ENDED>
    <CALL_END_REASON>NORMAL</CALL_END_REASON>
    <CALLER_INPUT>E</CALLER_INPUT>
    <FROM_EXT_VXML0>HFS.DCSS.CSCC.en-us.SG</FROM_EXT_VXML0>
    <FROM_EXT_VXML1>en-us</FROM_EXT_VXML1>
    <FROM_EXT_VXML2 />
    <FROM_EXT_VXML3 />
    <RTR_CL_KEY>00</RTR_CL_KEY>
    <RTR_SEQ_NUM>00</RTR_SEQ_NUM>
    <RTR_CL_KEY_DAY>00</RTR_CL_KEY_DAY>
    <RES1 />
    <RES2 />
    <RES3 />
    <RES4 />
    <RES5 />
    <RES6 />
    <RES7 />
    <RES8 />
    <RES9 />
    <RES10 />
  </IVR>
  <MENUDETAIL>
    <MENU>
      <MENUID>M1001</MENUID>
      <MENU_STARTTIME>04/02/2015 09:15:43</MENU_STARTTIME>
      <MENU_ENDTIME>04/02/2015 09:15:47</MENU_ENDTIME>
      <MENUOPTION>1</MENUOPTION>
      <MENUMODE>dtmf</MENUMODE>
      <MNURES1>NA</MNURES1>
      <MNURES2>NA</MNURES2>
    </MENU>
    <MENU>
      <MENUID>M1002</MENUID>
      <MENU_STARTTIME>04/02/2015 09:15:47</MENU_STARTTIME>
      <MENU_ENDTIME>04/02/2015 09:15:48</MENU_ENDTIME>
      <MENUOPTION>1</MENUOPTION>
      <MENUMODE>dtmf</MENUMODE>
      <MNURES1>NA</MNURES1>
      <MNURES2>NA</MNURES2>
    </MENU>
    <MENU>
      <MENUID>M1054</MENUID>
      <MENU_STARTTIME>04/02/2015 09:15:48</MENU_STARTTIME>
      <MENU_ENDTIME>04/02/2015 09:15:48</MENU_ENDTIME>
      <MENUOPTION>1</MENUOPTION>
      <MENUMODE>dtmf</MENUMODE>
      <MNURES1>NA</MNURES1>
      <MNURES2>NA</MNURES2>
    </MENU>
    <MENU>
      <MENUID>M1038</MENUID>
      <MENU_STARTTIME>04/02/2015 09:15:48</MENU_STARTTIME>
      <MENU_ENDTIME>04/02/2015 09:15:51</MENU_ENDTIME>
      <MENUOPTION>NA</MENUOPTION>
      <MENUMODE />
      <MNURES1>NA</MNURES1>
      <MNURES2>NA</MNURES2>
    </MENU>
    <MENU>
      <MENUID>D1003</MENUID>
      <MENU_STARTTIME>04/02/2015 09:15:51</MENU_STARTTIME>
      <MENU_ENDTIME>04/02/2015 09:15:59</MENU_ENDTIME>
      <MENUOPTION>198004947</MENUOPTION>
      <MENUMODE>dtmf</MENUMODE>
      <MNURES1>NA</MNURES1>
      <MNURES2>NA</MNURES2>
    </MENU>
    <MENU>
      <MENUID>M1039</MENUID>
      <MENU_STARTTIME>04/02/2015 09:15:59</MENU_STARTTIME>
      <MENU_ENDTIME>04/02/2015 09:16:03</MENU_ENDTIME>
      <MENUOPTION>1</MENUOPTION>
      <MENUMODE>dtmf</MENUMODE>
      <MNURES1>NA</MNURES1>
      <MNURES2>NA</MNURES2>
    </MENU>
    <MENU>
      <MENUID>D1004</MENUID>
      <MENU_STARTTIME>04/02/2015 09:16:03</MENU_STARTTIME>
      <MENU_ENDTIME>04/02/2015 09:16:15</MENU_ENDTIME>
      <MENUOPTION>359909410</MENUOPTION>
      <MENUMODE>dtmf</MENUMODE>
      <MNURES1>NA</MNURES1>
      <MNURES2>NA</MNURES2>
    </MENU>
    <MENU>
      <MENUID>M1041</MENUID>
      <MENU_STARTTIME>04/02/2015 09:16:15</MENU_STARTTIME>
      <MENU_ENDTIME>04/02/2015 09:16:17</MENU_ENDTIME>
      <MENUOPTION>1</MENUOPTION>
      <MENUMODE>dtmf</MENUMODE>
      <MNURES1>NA</MNURES1>
      <MNURES2>NA</MNURES2>
    </MENU>
  </MENUDETAIL>
  <EVENTDETAIL>
    <EVENT>
      <EVENTID>DC001</EVENTID>
      <EVENT_TIME>04/02/2015 09:15:47</EVENT_TIME>
      <EVNRES1 />
      <EVNRES2 />
    </EVENT>
  </EVENTDETAIL>
</IVRDATA>
Posted

There are tons of article available over the net that explains creating a column of data type xml in a SQL server table. Check this one, it explains well. Making Sense of the XML DataType in SQL Server 2005[^]
 
Share this answer
 
This is the code I used to insert XML packets into SQl table

SQL
insert into [dbo].[TBL_CALLDATA_MASTER] ( CallID,SessionID,CountryCode,RegionCode,FlowCode,ApplicationID,CallDateTime,CallData,Status)
values('84CAB60BABB711E4BDB9C7154A250D84','10.195.188.78.1423062943712.293.HFS_DCSS_CVP',NULL,NULL,NULL,'3',getdate(),'<IVRDATA>
  <IVR>
    <CALLID>84CAB60BABB711E4BDB9C7154A250D84</CALLID>
    <ICM_CALLID>00-00-00</ICM_CALLID>
    <SESSIONID>10.195.188.78.1423062943712.293.HFS_DCSS_CVP</SESSIONID>
    <APP_ID>3</APP_ID>
    <APP_TYPE>DCSS</APP_TYPE>
    <VXMLSERVER>10.195.188.78</VXMLSERVER>
    <DNIS>8523</DNIS>
    <CLI>914461084538</CLI>
    <CALL_STARTDATETIME>04/02/2015 09:15:43</CALL_STARTDATETIME>
    <CALL_ENDDATETIME>04/02/2015 09:16:23</CALL_ENDDATETIME>
    <LANGCODE>en-us</LANGCODE>
    <SKILLNAME>HFS.DCSS.CSCC.en-us.SG</SKILLNAME>
    <AGENT_TRANSFER>N</AGENT_TRANSFER>
    <ENDTYPE>CD</ENDTYPE>
    <DISPOSITION>CD</DISPOSITION>
    <HOW_CALL_ENDED>HANGUP</HOW_CALL_ENDED>
    <CALL_END_REASON>NORMAL</CALL_END_REASON>
    <CALLER_INPUT>E</CALLER_INPUT>
    <FROM_EXT_VXML0>HFS.DCSS.CSCC.en-us.SG</FROM_EXT_VXML0>
    <FROM_EXT_VXML1>en-us</FROM_EXT_VXML1>
    <FROM_EXT_VXML2 />
    <FROM_EXT_VXML3 />
    <RTR_CL_KEY>00</RTR_CL_KEY>
    <RTR_SEQ_NUM>00</RTR_SEQ_NUM>
    <RTR_CL_KEY_DAY>00</RTR_CL_KEY_DAY>
    <RES1 />
    <RES2 />
    <RES3 />
    <RES4 />
    <RES5 />
    <RES6 />
    <RES7 />
    <RES8 />
    <RES9 />
    <RES10 />
  </IVR>
  <MENUDETAIL>
    <MENU>
      <MENUID>M1001</MENUID>
      <MENU_STARTTIME>04/02/2015 09:15:43</MENU_STARTTIME>
      <MENU_ENDTIME>04/02/2015 09:15:47</MENU_ENDTIME>
      <MENUOPTION>1</MENUOPTION>
      <MENUMODE>dtmf</MENUMODE>
      <MNURES1>NA</MNURES1>
      <MNURES2>NA</MNURES2>
    </MENU>
    <MENU>
      <MENUID>M1002</MENUID>
      <MENU_STARTTIME>04/02/2015 09:15:47</MENU_STARTTIME>
      <MENU_ENDTIME>04/02/2015 09:15:48</MENU_ENDTIME>
      <MENUOPTION>1</MENUOPTION>
      <MENUMODE>dtmf</MENUMODE>
      <MNURES1>NA</MNURES1>
      <MNURES2>NA</MNURES2>
    </MENU>
    <MENU>
      <MENUID>M1054</MENUID>
      <MENU_STARTTIME>04/02/2015 09:15:48</MENU_STARTTIME>
      <MENU_ENDTIME>04/02/2015 09:15:48</MENU_ENDTIME>
      <MENUOPTION>1</MENUOPTION>
      <MENUMODE>dtmf</MENUMODE>
      <MNURES1>NA</MNURES1>
      <MNURES2>NA</MNURES2>
    </MENU>
    <MENU>
      <MENUID>M1038</MENUID>
      <MENU_STARTTIME>04/02/2015 09:15:48</MENU_STARTTIME>
      <MENU_ENDTIME>04/02/2015 09:15:51</MENU_ENDTIME>
      <MENUOPTION>NA</MENUOPTION>
      <MENUMODE />
      <MNURES1>NA</MNURES1>
      <MNURES2>NA</MNURES2>
    </MENU>
    <MENU>
      <MENUID>D1003</MENUID>
      <MENU_STARTTIME>04/02/2015 09:15:51</MENU_STARTTIME>
      <MENU_ENDTIME>04/02/2015 09:15:59</MENU_ENDTIME>
      <MENUOPTION>198004947</MENUOPTION>
      <MENUMODE>dtmf</MENUMODE>
      <MNURES1>NA</MNURES1>
      <MNURES2>NA</MNURES2>
    </MENU>
    <MENU>
      <MENUID>M1039</MENUID>
      <MENU_STARTTIME>04/02/2015 09:15:59</MENU_STARTTIME>
      <MENU_ENDTIME>04/02/2015 09:16:03</MENU_ENDTIME>
      <MENUOPTION>1</MENUOPTION>
      <MENUMODE>dtmf</MENUMODE>
      <MNURES1>NA</MNURES1>
      <MNURES2>NA</MNURES2>
    </MENU>
    <MENU>
      <MENUID>D1004</MENUID>
      <MENU_STARTTIME>04/02/2015 09:16:03</MENU_STARTTIME>
      <MENU_ENDTIME>04/02/2015 09:16:15</MENU_ENDTIME>
      <MENUOPTION>359909410</MENUOPTION>
      <MENUMODE>dtmf</MENUMODE>
      <MNURES1>NA</MNURES1>
      <MNURES2>NA</MNURES2>
    </MENU>
    <MENU>
      <MENUID>M1041</MENUID>
      <MENU_STARTTIME>04/02/2015 09:16:15</MENU_STARTTIME>
      <MENU_ENDTIME>04/02/2015 09:16:17</MENU_ENDTIME>
      <MENUOPTION>1</MENUOPTION>
      <MENUMODE>dtmf</MENUMODE>
      <MNURES1>NA</MNURES1>
      <MNURES2>NA</MNURES2>
    </MENU>
  </MENUDETAIL>
  <EVENTDETAIL>
    <EVENT>
      <EVENTID>DC001</EVENTID>
      <EVENT_TIME>04/02/2015 09:15:47</EVENT_TIME>
      <EVNRES1 />
      <EVNRES2 />
    </EVENT>
  </EVENTDETAIL>
</IVRDATA>','P');
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900