Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How can I add encoding in output XML file like "<?xml version="1.0" encoding="ISO-8859-1"?>" Expand / Collapse
Author
Message
Posted Thursday, December 12, 2013 9:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 28, 2014 8:19 AM
Points: 10, Visits: 78
Hello all:
Can someone tell me if I can add "<?xml version="1.0" encoding="ISO-8859-1"?>" in the first line of XML file? Currently it just shows <?xml version="1.0"> but it woudl give me trouble if it's French or other different encoding code languages when open it by using XML notepad.

EXEC xp_cmdshell 'bcp " select top 1 topic_code.col1, topic_code.col2 from lnc_contentmanager.dbo.wdph_topic_tag topic_code for xml path (''doc'')" queryout "e:\DropFolderFor365\OutFile2.xml" -T -c -C 28591'

Thanks in advance!
Jimmy
Post #1522372
Posted Thursday, December 12, 2013 11:05 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 10:05 PM
Points: 1,688, Visits: 2,272
Without knowing the details of the method you're using to generate said XML, it's hard to make a good choice about how best to go about it. If the XML you generate already has a record that might need to be replaced with the text you're looking for, that's a somewhat different problem from just adding this text at the beginning. Assuming you can at least get the XML text as individual records in some fashion (they don't need to be the XML data type, and plain old varchar or nvarchar would actually be preferable), then you can just:

SELECT '<?xml version="1.0" encoding="ISO-8859-1"?>' AS XML_TEXT
UNION ALL
SELECT XML_TEXT
FROM XML_RECORDSET

You'll have to supply the field name instead of XML_TEXT as well as the table name for your recordset. If you need to filter out the other text, then you'd need the recordset creation process to do that part of it. Assuming a recordset gets created, you might be able to just filter on the text value.

Can you provide more details on how this XML is being generated?


If you're just needing to a


Steve
(aka sgmunson)

Internet ATM Machine
Post #1522424
Posted Thursday, December 12, 2013 11:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 28, 2014 8:19 AM
Points: 10, Visits: 78
Its' a select statement directly from table with fields


Select doc.col1, doc.col2, doc.col3
from TestTable as doc
for xml path ('doc')

the result is one XML column or you can say it's one column, I didn't try your way but not working, see below:

SELECT ('<?xml version="1.0" encoding="iso8859-1"?>') for xml path ('')
UNION ALL
Select doc.col1, doc.col2, doc.col3
from TestTable as doc
for xml path ('doc')
Post #1522437
Posted Thursday, December 12, 2013 4:33 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 10:05 PM
Points: 1,688, Visits: 2,272
I'll assume your query produces a single record with the XML data type, and as the UNION ALL expects data types to match, and that my idea assumed a different scenario, this won't quite work as written, however..

You could use your query as a subquery as follows:

SELECT '<?xml version="1.0" encoding="iso8859-1"?>' +
(
SELECT doc.col1, doc.col2, doc.col3
FROM TestTable AS doc
FOR XML PATH ('doc')
)

Let me know if that works for you...


Steve
(aka sgmunson)

Internet ATM Machine
Post #1522519
Posted Friday, December 13, 2013 12:38 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 12:50 PM
Points: 1,061, Visits: 2,580
sgmunson (12/12/2013)
I'll assume your query produces a single record with the XML data type, and as the UNION ALL expects data types to match, and that my idea assumed a different scenario, this won't quite work as written, however..

You could use your query as a subquery as follows:

SELECT '<?xml version="1.0" encoding="iso8859-1"?>' +
(
SELECT doc.col1, doc.col2, doc.col3
FROM TestTable AS doc
FOR XML PATH ('doc')
)

Let me know if that works for you...


That'll give you a character string that consists of well-formed XML - you have to CAST the whole thing back to XML if you want typed output:
SELECT CAST((SELECT '<?xml version="1.0" encoding="iso8859-1"?>' +
(
SELECT doc.col1, doc.col2, doc.col3
FROM TestTable AS doc
FOR XML PATH ('doc')
)) AS XML)

Also, if you specify an encoding like this but the result of the inner SELECT . . . FOR XML contains characters that are invalid for that encoding, you may get an error that SQL Server was unable to switch the encoding.


Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Post #1522840
Posted Friday, December 13, 2013 12:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 28, 2014 8:19 AM
Points: 10, Visits: 78
No, it would not work because it inside the root. This XML has to be outside the root.

Actually the previous idea was good. I could make it to work perfectly.

SELECT replace (replace ((SELECT ('<?xml version="1.0" encoding="iso8859-1"?>')
for xml path ( ('')) , '<','<') ,'>', '>')
UNION ALL
select (Select doc.col1, doc.col2, doc.col3
from TestTable as doc for xml path ('doc') )

It would generate 2 records in the XML file but it's perfect when use an XML editor to open it. It resolved my issue that XML file contains double byte chars.

Thanks for the great idea Steve!

Jimmy
Post #1522846
Posted Friday, December 13, 2013 12:50 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 28, 2014 8:19 AM
Points: 10, Visits: 78
I think the web app changed my sql statement

if looks the same it should be replacing "& lt;" to "<" and "& gt:' to "<", no space between & and lt; otherwise it would be translated again.

select replace (replace ((SELECT ('<?xml version="1.0" encoding="iso8859-1"?>') for xml path ('')) , '<','<') ,'>', '>')
Post #1522850
Posted Monday, December 16, 2013 7:07 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 10:05 PM
Points: 1,688, Visits: 2,272
Jason,

Thanks for the correction. I hadn't really given that much thought, and had made one of those assumptions that can just show up out of an apparent nowhere and do a "gotcha dance" on you...


Steve
(aka sgmunson)

Internet ATM Machine
Post #1523205
Posted Monday, December 16, 2013 7:08 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 10:05 PM
Points: 1,688, Visits: 2,272
You're quite welcome...

Jim88 (12/13/2013)
No, it would not work because it inside the root. This XML has to be outside the root.

Actually the previous idea was good. I could make it to work perfectly.

SELECT replace (replace ((SELECT ('<?xml version="1.0" encoding="iso8859-1"?>')
for xml path ( ('')) , '<','<') ,'>', '>')
UNION ALL
select (Select doc.col1, doc.col2, doc.col3
from TestTable as doc for xml path ('doc') )

It would generate 2 records in the XML file but it's perfect when use an XML editor to open it. It resolved my issue that XML file contains double byte chars.

Thanks for the great idea Steve!

Jimmy


Steve
(aka sgmunson)

Internet ATM Machine
Post #1523207
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse