December 12, 2013 at 9:20 am
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
December 12, 2013 at 11:05 am
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 12, 2013 at 11:36 am
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')
December 12, 2013 at 4:33 pm
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 13, 2013 at 12:38 pm
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
December 13, 2013 at 12:43 pm
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
December 13, 2013 at 12:50 pm
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 ('')) , '<','<') ,'>', '>')
December 16, 2013 at 7:07 am
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 16, 2013 at 7:08 am
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply