none
Create Table from BCP Formatted File RRS feed

  • Question

  • I have a number of xml based table files that were generated by BCP and TXT files containing data for those tables.

    How do I create tables in SQL Server using the .XML file and then import in data from .TXT file?

    I can import data to a new table and rename columns accordingly but I want to see if I can avoid entering column names manually.

    Thursday, August 22, 2019 10:14 PM

All replies

  • Hi MikeGanem2,

    Please share sample of a pair of matching XML and *.txt files.

    Thursday, August 22, 2019 10:36 PM
  • <?xml version="1.0"?>
    <BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
     <RECORD>
      <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="12"/>
      <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="12"/>
      <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="1" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
      <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
      <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
      <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
     </RECORD>
     <ROW>
      <COLUMN SOURCE="1" NAME="NAM_IDN" xsi:type="SQLINT"/>
      <COLUMN SOURCE="2" NAME="PER_IDN" xsi:type="SQLINT"/>
      <COLUMN SOURCE="3" NAME="TYP_COD" xsi:type="SQLVARYCHAR"/>
      <COLUMN SOURCE="4" NAME="NAM_TXT" xsi:type="SQLVARYCHAR"/>
      <COLUMN SOURCE="5" NAME="LNA_TXT" xsi:type="SQLVARYCHAR"/>
      <COLUMN SOURCE="6" NAME="FNA_TXT" xsi:type="SQLVARYCHAR"/>
     </ROW>
    </BCPFORMAT>

    6201	3330	S	MOON,JEFFREY L	MOON	JEFFREY L
    6202	3330	B	MOON,JEFFREY LEE	MOON	JEFFREY LEE
    22556	12077	S	HERNANDEZ,EDWARD D	HERNANDEZ	EDWARD D
    22557	12077	B	HERNANDEZ,EDWARD DOMINGUEZ	HERNANDEZ	EDWARD DOMINGUEZ
    3337067 12077	S	HERNANDEZ,EDWARD	HERNANDEZ	EDWARD
    395601	187204	S	HOPKINS,C W	HOPKINS	C W
    395602	187204	S	HOPKINS,COY W	HOPKINS	COY W
    395603	187204	B	HOPKINS,COY WILLIAM	HOPKINS	COY WILLIAM
    624683	286009	S	CHACON,DANIEL R	CHACON	DANIEL R

    Friday, August 23, 2019 2:01 PM
  • Hi MikeGanem2,

    I saved the XML provided as 'e:\Temp\BCPFORMAT.xml' file on the file system.

    Check it out.

    SET NOCOUNT ON;
    DECLARE @xml XML
       , @sql NVARCHAR(MAX)
       , @tableName VARCHAR(30) = 'xyz'
       , @XMLfileName VARCHAR(256) = 'e:\Temp\BCPFORMAT.xml';
    
    DECLARE @tbl TABLE (
    	columnID INT
    	, columnName VARCHAR(50)
    	, columnType VARCHAR(50)
    	, columnLength INT
    );
    
    SET @sql = N'SELECT @xmlOut = XmlDoc FROM OPENROWSET (BULK ' + QUOTENAME(@XMLfileName,NCHAR(39)) + ', SINGLE_BLOB) AS Tab(XmlDoc)';
    EXEC master.sys.sp_executesql @sql, N'@xmlOut XML OUTPUT', @xmlOut = @xml OUTPUT;
    
    ;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/bulkload/format'), rs AS
    (
    	SELECT c.value('@NAME', 'VARCHAR(50)') AS [columnName]
    	   , c.value('@xsi:type', 'VARCHAR(50)') AS [columnType]
    	   , c.value('@SOURCE', 'INT') AS [columnID]
    	FROM @xml.nodes('/BCPFORMAT/ROW/COLUMN') AS t(c)
    )
    , rs2 AS
    (
    	SELECT c.value('@ID', 'INT') AS [columnID]
    	   , c.value('@MAX_LENGTH', 'VARCHAR(50)') AS [columnLength]
    	FROM @xml.nodes('/BCPFORMAT/RECORD/FIELD') AS t(c)
    
    )
    INSERT INTO @tbl
    SELECT rs.columnID
    	, rs.columnName
    	, rs.columnType
    	, rs2.columnLength 
    FROM rs INNER JOIN rs2 ON rs2.columnID = rs.columnID;
    
    -- SELECT * FROM @tbl;
    
    DECLARE @RowCount INT = (SELECT COUNT(*) FROM @tbl);
    SET @SQL = 'CREATE TABLE '+ @tableName + ' (' + NCHAR(13) + NCHAR(10);
    
    WHILE @RowCount > 0 BEGIN
    	SELECT @SQL = @SQL + columnName + ' ' +
    		CASE columnType
    			WHEN 'SQLINT' THEN 'INT'  
    			WHEN 'SQLVARYCHAR' THEN 'VARCHAR'
    			-- maybe needed more data types
    			ELSE 'VARCHAR'  
    		END +
    		IIF(columnType='SQLVARYCHAR', '(' + CAST(columnLength AS VARCHAR) + ')', '')
    		+ ', ' + NCHAR(13) + NCHAR(10)
       FROM @tbl 
       ORDER BY columnID DESC OFFSET @RowCount - 1 ROWS FETCH NEXT 1 ROWS ONLY;
    
       SET @RowCount -= 1;
    END;
    
    SET @SQL = LEFT(@SQL, LEN(@SQL) - 4) + ');' + NCHAR(13) + NCHAR(10)
    
    -- SQL is ready, see it in messages pane
    PRINT @sql;
    
    -- ready to execute, uncomment next line
    -- EXEC(@SQL);

    Output:
    CREATE TABLE xyz (
    NAM_IDN INT, 
    PER_IDN INT, 
    TYP_COD VARCHAR(1), 
    NAM_TXT VARCHAR(30), 
    LNA_TXT VARCHAR(30), 
    FNA_TXT VARCHAR(30));

    Friday, August 23, 2019 4:14 PM
  • Hi MikeGanem2,

    Somewhat shorter version without temporary table variable with the same outcome.

    SET NOCOUNT ON;
    DECLARE @xml XML
       , @sql NVARCHAR(MAX)
       , @tableName VARCHAR(30) = 'xyz'
       , @XMLfileName VARCHAR(256) = 'e:\Temp\BCPFORMAT.xml';
    
    SET @sql = N'SELECT @xmlOut = XmlDoc FROM OPENROWSET (BULK ' + QUOTENAME(@XMLfileName,NCHAR(39)) + ', SINGLE_BLOB) AS Tab(XmlDoc)';
    EXEC master.sys.sp_executesql @sql, N'@xmlOut XML OUTPUT', @xmlOut = @xml OUTPUT;
    
    -- count total number of columns
    DECLARE @cnt INT, @i INT;
    SET @cnt = @xml.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/bulkload/format"; count(/BCPFORMAT/RECORD/FIELD)', 'INT');
    
    SET @SQL = 'CREATE TABLE '+ @tableName + ' (' + NCHAR(13) + NCHAR(10);
    
    -- loop column by column
    SET @i = 1;
    WHILE @i <= @cnt BEGIN
       ;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/bulkload/format')
          SELECT @SQL = @SQL + c.value('@NAME', 'VARCHAR(50)') + ' ' +
    		CASE c.value('@xsi:type', 'VARCHAR(50)') 
    			WHEN 'SQLINT' THEN 'INT'  
    			WHEN 'SQLVARYCHAR' THEN 'VARCHAR'
    			-- maybe needed more data types
    			ELSE 'VARCHAR'  
    		END +
    		IIF(c.value('@xsi:type', 'VARCHAR(50)')='SQLVARYCHAR', '(' + 
    			c.value('(/BCPFORMAT/RECORD/FIELD[position() = sql:variable("@i")]/@MAX_LENGTH)[1]', 'VARCHAR(50)') + ')', '')
    		+ ', ' + NCHAR(13) + NCHAR(10)
          FROM @xml.nodes('/BCPFORMAT/ROW/COLUMN[position() = sql:variable("@i")]') AS t(c);
    
       SET @i += 1;
    END
    
    SET @SQL = LEFT(@SQL, LEN(@SQL) - 4) + ');' + NCHAR(13) + NCHAR(10)
    
    -- SQL is ready, see it in messages pane
    PRINT @sql;
    
    -- ready to execute, uncomment next line
    -- EXEC(@SQL);
    Friday, August 23, 2019 5:10 PM
  • Thanks but this is not what I am looking for.
    Friday, August 23, 2019 6:18 PM
  • Hi MikeGanem2,

    >> "...How do I create tables in SQL Server using the .XML file

    I can import data to a new table and rename columns accordingly but I want to see if I can avoid entering column names manually...."

    And the entire post is named as "Create Table from BCP Formatted File".
    Friday, August 23, 2019 6:52 PM
  • Hi MikeGanem2,

    Maybe this is what you are looking for:

    SELECT * INTO #tableName FROM OPENROWSET(BULK 'e:\Temp\DataCow.txt' , FORMATFILE = 'e:\Temp\DataCow.xml' , ERRORFILE = 'e:\Temp\DataCow.err' , FIRSTROW = 2 -- real data starts on the 2nd row , MAXERRORS = 100 ) AS t1;

    SELECT * FROM #tableName;


    Friday, August 23, 2019 6:58 PM