Export to XML question

[Eric S]

Hi All,

The below is a test code to export xml file. The fields Comment and Tip
hold xHTML code. The problem occurs if I add the Tip field then I get the
following error:

The XML page cannot be displayed
Required white space was missing. Error processing resource
'file:///C:/1.xml'. Line 1, Position 646

It seems that XM file cannot fully exported and some text is missing.
I apprecaite any input.

DECLARE @SQL VARCHAR(1024)
DECLARE @sqlFlds1 VARCHAR(225)
DECLARE @sqlFlds2 VARCHAR(225)
DECLARE @sqlFlds3 VARCHAR(225)
DECLARE @sqlFlds4 VARCHAR(225)

DECLARE @sqlString3 VARCHAR(225)

SET @sqlFlds2 = ' "SELECT
Sc.SearchCategory,Sc.SearchOption,Sc.DisplayOptionOrder,Sc.SearchCategories_PK,'
SET @sqlFlds3 = 'Sc.Comment,'
SET @sqlFlds4 = 'Sc.Tip '

SET @sqlstring3 ='FROM HumanFindAdmin.dbo.SearchCategories Sc WHERE
SearchOptions_FK=1 FOR XML AUTO, ROOT(''Menu_Options'')"'

SET @SQL = 'bcp ' + @sqlFlds2 + @sqlFlds3 + @sqlFlds4 + @sqlString3 + '
Queryout "C:\1.xml" -c -T -SHuman'
EXEC Master..xp_cmdshell @SQL

Regards,

Eric

[Erland Sommarskog]

Eric S (xxx_n...@Hotmail.com) writes:
> The below is a test code to export xml file. The fields Comment and Tip
> hold xHTML code. The problem occurs if I add the Tip field then I get the
> following error:
>
> The XML page cannot be displayed
> Required white space was missing. Error processing resource
> 'file:///C:/1.xml'. Line 1, Position 646
>
> It seems that XM file cannot fully exported and some text is missing.
> I apprecaite any input.

It may help if you add ,TYPE to for FOR XML clause, but I'm not sure.
I would rather look at another way of creating the file than using
BCP and xp_cmdshell anyway.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Eric S]

Thanks Erland.
I made it to work usimng .net. I hope MS will improve that feature in the
next service pack.

Regards,

Eric

[Uri Dimant]

Did you mean in the next release:-)

"Eric S" <xxx_n...@Hotmail.com> wrote in message
news:e4NkpnWi...@TK2MSFTNGP04.phx.gbl...

[Eric S]

Hi Uri,

I mean the next release it could be something that I can use.<s>
So this way the entire sql statement plus the export process SCRIPT is all
encapsulated in one SP one place and one technology.

Regards,

Eric

"Uri Dimant" <ur...@iscar.co.il> wrote in message
news:OzaF9Uei...@TK2MSFTNGP06.phx.gbl...

[Erland Sommarskog]

Eric S (xxx_n...@Hotmail.com) writes:
> I mean the next release it could be something that I can use.<s>
> So this way the entire sql statement plus the export process SCRIPT is all
> encapsulated in one SP one place and one technology.

A BULK EXPORT command should certinly be welcome. You can vote here:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=224026

[Eric S]

That is a good idea.

Regards,

Eric

"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9CF2D429C...@127.0.0.1...