We have a table as file details which has file name and XMLfile of XML data type field which has format in XML.
While using bulk insert instead of “with formatfile = ‘file path” need to use XMLfile field from file details table.
How we can achieve?
We have a table as file details which has file name and XMLfile of XML data type field which has format in XML.
While using bulk insert instead of “with formatfile = ‘file path” need to use XMLfile field from file details table.
How we can achieve?
While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
Hi Karthik-7075,
How are things going?
Did the answer help you? If not, please reply and share us more information in order to provide further help.
Please refer to this article which might help.
Best Regards,
Amelia
So you want to use the XML value in the column as the format file for the BULK INSERT statement?
Since you cannot easily write files from SQL Server to disk, you may want to reconsider the design.
You could use xp_cmdshell and run BCP to export the XML file to disk, but it is certainly a bit of a kludge.
An alternative is to control the whole thing from a client-side program that uses BCP to load the file. The program could easily read the column from the table and write to disk and BCP could then use the format file. The program could be a PowerShell script.
Building a dynamic format XML string and want to utilize in bulk insert command.
XML format
<BCPFormat>
<RECORD>
<FIELD ID="1" xsi:type="CharFixed" LENGTH="2"/>
,,....
..
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="ID" xsi:type="SQLSMALLINT"/>
....
.
</BCPFormat>
How this can be achieved ?
As I said in my previous post, since you cannot easily write files from SQL Server to disk, you may want to reconsider the design.
You may also want to re-read the remaining paragraphs of my post above.
Hi Karthik-7075,
Please refer to Using a format file to bulk import data which might be helpful.
Best Regards,
Amelia
16 people are following this question.