Tricky SQL BCP syntax when using XMLRECORD.value in a query

• AronsonAa

  SSC Rookie

  Points: 43

  More actions

  November 5, 2015 at 1:06 pm

  #97469

  I have the following SQL SELECT statment that works fine in a Query window

  SELECT XMLRECORD.value('(/row/c1/text())[1]','varchar(MAX)') FROM SOMEDB.dbo.TheXMLtable

  However I'm having difficulty formatting this query when trying to export the table out to a flat file on disk when using BCP.

  I know I have to 'escape' the single quotes, so this yields (There are double quotes around the select, all others are Single quotes)

  BCP "SELECT XMLRECORD.value(''/row/c1/text())[1]'',''varchar(MAX)'') FROM SOMEDB.dbo.TheXMLtable" QUERYOUT "dfeoutAa.txt" -T -c

  ___^DblQuote______________^2SingleQuote____^2^SingleQuote ^2SingleQuote_________________^DblQuote__^DblQuote__^DblQuote

  This is yielding the error "[SQL Server]'text' is not a recognized built-in function name."

  I've spent an hour trying various variations of single quotes but so far have not been able to find the correct method.

  Does anyone understand what's missing here, or can suggest an alternate way of exporting the file ?

  Source row looks like;

  key766.33<row id="key766.33" xml:space="preserve"><c1>value1|value2|value3|valuexx</c1></row>

  Export expcted to look like

  value1|value2|value3|valuexx

  I apprecate the help. Thanks, Aa