July 17, 2017 at 4:14 pm
I created an SSIS Export Package to output to XML. For some reason my SQL Code is wanted to change a lower case letter to an upper case letter on the output even though on the SQL it doesn't show upper. Here is my SQL
SELECT '<?xml version="1.0" encoding="UTF-8"?>' +
CAST((select externalId1 as [@externalStudentId1],
convert(varchar,getdate(),126) as [documentData/@dateCreated],
convert(varchar,getdate(),126) as [documentData/@dateReceived],
'Student Debt Letter Sent' as [documentData/@documentInformation],
convert(varchar,getdate(),126) as [documentData/@documentRequirementStatusChangeOnDate],
'STUDENT' as [documentData/@documentScope],
'SDLEmail' as [documentData/@externalDocumentId],
'RECEIVED' as [documentData/@status]
from dbo.StudentDebtData
for xml path('student'), ROOT('students'))
As VARCHAR(MAX)) As XMLData
The output looks like this:
<?xml version="1.0" encoding="UTF-8" ?> - <students>- <student externalStudentID1="1234567"> <documentData dateCreated="2017-07-17T16:13:44.807" dateReceived="2017-07-17T16:13:44.807" documentInformation="Student Debt Letter Sent" documentRequirementStatusChangeOnDate="2017-07-17T16:13:44.807" documentScope="STUDENT" externalDocumentID="SDLEmail" status="RECEIVED" /> </student>
externalStudentID1 should be externalStudentId1
externalDocumentID should be externalDocumentId
I thought maybe I had typed in something wrong so I looked at the code and it is set to lower case "d" but yet it wants to put in "D". When I have it this way and I try to load the XML into another system, I get errors that one is not declared and one is missing. When I change it to be what it should be the XML loads perfectly in the other system.
July 18, 2017 at 3:30 am
I was unable to reproduce the behavior in SQL Server 2016. Before I dig into SSIS, can you answer a couple questions. What version of SQL Server are you running this query (the connection in SSIS)? What kind of connection (ADO.Net, Ole DB, SQLConnection in C# code) What version of SSIS? How are you outputting this XML (Execute SQL Task, Script Task, XML task)? What are the relevant settings on that task (Execute SQL: ResultSet property)
I made a slight change so that it is easy to reproduce the results.
SELECT '<?xml version="1.0" encoding="UTF-8"?>' +
CAST((select externalId1 as [@externalStudentId1],
convert(varchar,getdate(),126) as [documentData/@dateCreated],
convert(varchar,getdate(),126) as [documentData/@dateReceived],
'Student Debt Letter Sent' as [documentData/@documentInformation],
convert(varchar,getdate(),126) as [documentData/@documentRequirementStatusChangeOnDate],
'STUDENT' as [documentData/@documentScope],
'SDLEmail' as [documentData/@externalDocumentId],
'RECEIVED' as [documentData/@status]
from (values(1)) as v(externalId1)
for xml path('student'), ROOT('students'))
As VARCHAR(MAX)) As XMLData;
Russel Loski, MCSE Business Intelligence, Data Platform
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply