Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How can I add encoding in output XML file like "<?xml version="1.0" encoding="ISO-8859-1"?>"


How can I add encoding in output XML file like "<?xml version="1.0" encoding="ISO-8859-1"?>"

Author
Message
Jim88
Jim88
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 89
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
sgmunson
sgmunson
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3585 Visits: 3996
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)
Smile Smile Smile
Health & Nutrition
Jim88
Jim88
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 89
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')
sgmunson
sgmunson
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3585 Visits: 3996
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)
Smile Smile Smile
Health & Nutrition
wolfkillj
wolfkillj
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1214 Visits: 2582
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
Jim88
Jim88
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 89
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
Jim88
Jim88
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 89
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 ('')) , '<','<') ,'>', '>')
sgmunson
sgmunson
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3585 Visits: 3996
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)
Smile Smile Smile
Health & Nutrition
sgmunson
sgmunson
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3585 Visits: 3996
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)
Smile Smile Smile
Health & Nutrition
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search