Hi guys and thank you for the help
Im still at entry level, and need some help on something.
So im processing alot of stuff and im getting alot errors:
"FOR XML could not serialize the data for node 'text' because it contains a character (0x0012) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive."
I have a insert proc that inserts body text into a table and when i pull data that includes the text from this tblDescriptionText when i generate my xml, it breaks giving error above.
so i can replace this easy using
Replace(ColumnName, '0012' , '') but i dont know what character 0x0012 looks like or character 0x0016 and etc.
I want to change my insert Proc : To replace other funny characters but im looking for maybe a list of funny characters that xml doesnt accept, so i can include it into my proc, because im already eliminating double spaces here.
[code]
CREATE proc [dbo].[PRocNameInsert]
@TransactionID int,
@text varchar(800),
@Subject varchar(26)
AS
set @text = REPLACE(REPLACE(REPLACE
(LTRIM(RTRIM(@text)),' ',' '+CHAR(7)),CHAR(7)+' ',''),CHAR(7),'')
set @Subject = REPLACE(REPLACE(REPLACE
(LTRIM(RTRIM(@Subject)),' ',' '+CHAR(7)),CHAR(7)+' ',''),CHAR(7),'')
Insert into dbo.description
(
transactionID,
[text],
[subject]
)
Values
(
@TransactionID ,
CASE WHEN LTRIM(RTRIM(@text)) = '' then null else LTRIM(RTRIM(@text)) end,
CASE WHEN LTRIM(RTRIM(@Subject)) = '' then null else LTRIM(RTRIM(@Subject)) end
)
[/code]
Or would you suggest instead of remove the characters i should encode them which to my understanding is this by what encoding special characters for xml, correct me if im wrong.
Replace(NAME, '<', '<')
But i would still need some sort of list of characters that break xml.
Any suggestions, list, or what my options are solution would be big help, thank you.
p.s if your looking for how i generate my xml:
[code]
DECLARE @XmlOutput xml
SET @XmlOutput =(
select (
select collectionDetails.suite '@suite',
(
select
transactions.transactionID '@transactionID',
case when transactions.sender = '' then null else transactions.sender end as sender,
case when transactions.senderUser = '' then null else transactions.senderUser end as senderUser,
case when transactions.recipient = '' then null else transactions.recipient end as recipient,
case when transactions.recipientUser = '' then null else transactions.recipientUser end as recipientUser,
convert(varchar, transactions.transDate,112) as date,
transactions.version,
(Select
(Select
Case when description.subject = '' then null else description.subject end as subject,
Case when description.text = '' then null else description.text end as text
From description description
where description.transactionID = transactions.transactionID
for xml path('description'),Type)
)
from transactions transactions
where transactions.transactionID=@TransctionId
for xml path('transaction'),type)
from collectionDetails collectionDetails
for xml path('collectionDetails'),type)
for xml path('collection'))
SELECT @XmlOutput as 'RecalXML'
[/code]