Please do not double-post. Direct comments here: http://www.sqlservercentral.com/Forums/Topic1082871-21-1.aspx

1. In an SSMS query window run this while in Grid Results mode (Ctrl+G):

SELECT CONVERT(XML,'<?xml version="1.0" encoding="UTF-8" ?>

<File>

<Row>

<Column Name="JobID">2010-1022</Column>

<Column Name="Folder">Closed (Filled)</Column>

<Column Name="NumberofOpenings">1</Column>

<Column Name="NumberofOpeningsRemaining">0</Column>

<Column Name="BusinessCardTitle">Director of...

Not that you won't receive help here but you may want to post this question in one of the Reporting Services forums: http://www.sqlservercentral.com/Forums/Group416.aspx

SQL Server supports casting strings containing exponential notations to approximate data types:

SELECT CAST('5.01217e+006' AS REAL) AS string_to_real,

CAST('5.01217e+006' AS FLOAT) AS string_to_float

edit:...

I would recommend handling this kind of thing outside the database...e.g. pull the resultset into a memory-resident data structure (like an ADO.NET DataTable) then remove all columns from the data...

It sounds like maybe...

May I ask how you ended up with delimited strings in a column? How is this data entering the system? Tools like BCP, BULK INSERT and SSIS are designed to...

Jeff Moden (3/23/2011)

---

In general, I disagree. 😀 There are some very easy steps that can...

belgarion (3/22/2011)

---

Sorry guys. Left out the OPTION (RECOMPILE) in my haste. Added it in. Actually, in many situations you actually find through your performance testing with multiple parameter combinations (which...

sku370870 (3/22/2011)

---

Just as a matter of curiousity ... I know I'll get shot down in flames ... but is what is posted above different from ...

SELECT [SalesOrderID], [OrderDate], [Status],...

Jinx! Diet Coke please Wayne 😎

Just passing along the knowledge...Gail's article is the de facto standard on the topic.

In general dynamic sql is a bad idea and you should avoid it...however your example code is building "catch all query". Have a look at this article:

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/%5B/url%5D

In your case the...

BCP was designed to be fast...not tolerant of bad data or feature-rich.

If you have data quality issues then you can ask for better data or cleanse it after you...

GSquared (3/22/2011)

---

I could be wrong (been a while since I used it), but I seem to remember that xp_cmdshell needs UNCs, instead of mapped drives. Is "G" a mapped...

Try this:

IF OBJECT_ID('test') > 0

DROP TABLE test

GO

CREATE TABLE test (col VARCHAR(20))

GO

INSERT test

(

...