none
Convert data into xml format

    Question

  • I have the following sample data.

    "Hour","User ID (evar3)","Report Launch (evar6)","Operating Systems","OS Type","Mobile Device Type","App Type (evar4)","Report Launch (event2)"
    "January 12, 2015, Hour 0","bwaldele","By Fund","mobile ios 7.1.2","apple ios","Tablet","retail",1
    "January 12, 2015, Hour 0","bwaldele","By Territory","mobile ios 7.1.2","apple ios","Tablet","retail",2
    "January 12, 2015, Hour 0","bwaldele","Top Redemptions (Rolling 30-Day)","mobile ios 7.1.2","apple ios","Tablet","retail",1
    "January 12, 2015, Hour 0","bwaldele","Top Sales (Rolling 30-Day)","mobile ios 7.1.2","apple ios","Tablet","retail",1
    "January 12, 2015, Hour 0","bwaldele","Top Sales (Yesterday)","mobile ios 7.1.2","apple ios","Tablet","retail",2
    "January 12, 2015, Hour 2","abobker","Top Sales (Rolling 30-Day)","mobile ios 8.1.2","apple ios","Tablet","retail",1
    "January 12, 2015, Hour 2","abobker","Top Sales (Yesterday)","mobile ios 8.1.2","apple ios","Tablet","retail",1
    "January 12, 2015, Hour 2","bwaldele","By Territory","mobile ios 7.1.2","apple ios","Tablet","retail",3

    The first row is header. We get the data as a single blob where the column delimiter is ',' and row delimiter is CHAR(10). I am trying to load data into a table but unable to parse this out. Any idea how to get this data as xml format

    <data>

    <row>

    <hour> </hour>

    <userid> </userid>

    <report launch> </report launch>...

    </row>

    </data>

    Tuesday, February 3, 2015 9:24 PM

Answers

  • yes Bulk insert is not that smart but you can use a format file to import the data or the other way would be using SSIS.

    the format file will look like this :

    10.0
    8
    1       SQLCHAR             0       100     "\",\""     1     Hour                                 SQL_Latin1_General_CP1_CI_AS
    2       SQLCHAR             0       50      "\",\""     2     UserID                               SQL_Latin1_General_CP1_CI_AS
    3       SQLCHAR             0       100     "\",\""     3     ReportLaunch6                        SQL_Latin1_General_CP1_CI_AS
    4       SQLCHAR             0       100     "\",\""     4     OperatingSystems                     SQL_Latin1_General_CP1_CI_AS
    5       SQLCHAR             0       50      "\",\""     5     OSType                               SQL_Latin1_General_CP1_CI_AS
    6       SQLCHAR             0       100     "\",\""     6     MobileDeviceType                     SQL_Latin1_General_CP1_CI_AS
    7       SQLCHAR             0       50      "\","     7     AppType                              SQL_Latin1_General_CP1_CI_AS
    8       SQLCHAR             0       12      "\n"   8     ReportLaunch2     

    and then you can use :

    BULK INSERT yrtable
    FROM 'you data file location'
    WITH
    (
    FORMATFILE = 'your format file location',
    FIRSTROW=2
    )

    Reference lniks :

    Bulk Insert

    Creating format file


    Thanks and regards, Rishabh K


    Wednesday, February 4, 2015 7:24 AM

All replies


  • Try this...You can start from here

    Save the sample data into input.csv under some folder(c:\blog) and run the below SQL

    Sample data of input.csv

    "Hour","UserID","ReportLaunch"
    "January 12, 2015, Hour 0","bwaldele","By Fund"
    "January 12, 2015, Hour 0","bwaldele","By Territory"

    SQL

    sp_configure 'Ad Hoc Distributed Queries',1 Go reconfigure Go SELECT * FROM OPENROWSET ( 'MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:\blog\;' , 'SELECT * from "input.csv"' );

    OR

    select
    [hour],
    UserID,
    [ReportLaunch]
    from openrowset('MSDASQL'
                   ,'Driver={Microsoft Access Text Driver (*.txt, *.csv)}; 
                       DefaultDir=C:\blog\'              
                   ,'select * from "input.CSV"') T
    FOR XML AUTO
                 

    Output:-

    <T hour="January 12, 2015, Hour 0" UserID="bwaldele" ReportLaunch="By Fund" />

    --Prashanth


    Tuesday, February 3, 2015 10:00 PM
  • Thanks, Prashanth. We are not allowed to do Distributed Queries and that is the reason. The only option that I have is getting the csv file using bulk insert as a single blob and parse out the columns. The data comes with " as text qualifier. But the issue is BULK INSERT is not intelligent enough to take text qualifier into consideration so, its splitting out a single column into multiple columns inside the qualifier which is making the statement to fail.

    The other option that I could try is using a format file and that should be pretty much straight forward but we are not allowed to.

    What are the other viable options?

    Tuesday, February 3, 2015 11:10 PM
  • yes Bulk insert is not that smart but you can use a format file to import the data or the other way would be using SSIS.

    the format file will look like this :

    10.0
    8
    1       SQLCHAR             0       100     "\",\""     1     Hour                                 SQL_Latin1_General_CP1_CI_AS
    2       SQLCHAR             0       50      "\",\""     2     UserID                               SQL_Latin1_General_CP1_CI_AS
    3       SQLCHAR             0       100     "\",\""     3     ReportLaunch6                        SQL_Latin1_General_CP1_CI_AS
    4       SQLCHAR             0       100     "\",\""     4     OperatingSystems                     SQL_Latin1_General_CP1_CI_AS
    5       SQLCHAR             0       50      "\",\""     5     OSType                               SQL_Latin1_General_CP1_CI_AS
    6       SQLCHAR             0       100     "\",\""     6     MobileDeviceType                     SQL_Latin1_General_CP1_CI_AS
    7       SQLCHAR             0       50      "\","     7     AppType                              SQL_Latin1_General_CP1_CI_AS
    8       SQLCHAR             0       12      "\n"   8     ReportLaunch2     

    and then you can use :

    BULK INSERT yrtable
    FROM 'you data file location'
    WITH
    (
    FORMATFILE = 'your format file location',
    FIRSTROW=2
    )

    Reference lniks :

    Bulk Insert

    Creating format file


    Thanks and regards, Rishabh K


    Wednesday, February 4, 2015 7:24 AM