Search code examples
sqlsql-serverxmlbcp

Unable to extract XML Data from SQL Server using bcp

I am trying to extract an xml column value from a sql server table via bcp, but I am facing the below error.

Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]SELECT failed beca use the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Veri fy that SET options are correct for use with indexed views and/or indexes on com puted columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

This is the query i am using via bcp 

select EventLog.payload.value('(/EventData/Pin)[1]', 'varchar(10)') 
as payload_pin 
from usp.EventLog

The above query is working in sql server but not via bcp. Has anyone encountered such scenario?

Solution

  • bcp "SET QUOTED_IDENTIFIER ON ;select ColumnName.value('(/EventData/Pin)[1]', 'varchar(10)') as pin;" queryout data.txt