XML in SQL: Element values evaluated differently when using table column rather than string

Wednesday, November 4, 2015

I'm trying to generate some XML via SQL for load testing, but I'm having trouble constructing the XML correctly. This is what I have:



-- Generate XML from table
SELECT id AS '@id',
action AS '@action',
actionTime AS '@actionTime',
firstName AS '@firstName',
lastName AS '@lastName',
email AS '@email',
(SELECT isActive AS '@isActive',
actionTime AS '@actionTime',
displayName AS '@displayName',
identifierId
FOR XML PATH('identifier'), TYPE
)
FROM scratch.dbo.Identities
FOR XML PATH('user'), ROOT('Users')


This generates XML that looks like this:



<Users>
<user id="QALoadTest1" action="add" actionTime="2015-11-01T00:00:00" firstName="Load" lastName="Test1" email="QALoadTest1@loadtest.com">
<identifier isActive="1" actionTime="2015-11-01T00:00:00" displayName="Load Test 1">
<identifierId>1111111111</identifierId>
</identifier>
</user>
</Users>


If I pass a string as the identifier value, the XML is generated correctly:



SELECT id AS '@id',
action AS '@action',
actionTime AS '@actionTime',
firstName AS '@firstName',
lastName AS '@lastName',
email AS '@email',
(SELECT isActive AS '@isActive',
actionTime AS '@actionTime',
displayName AS '@displayName',
'11111111111'
FOR XML PATH('identifier'), TYPE
)
FROM scratch.dbo.Identities
FOR XML PATH('user'), ROOT('Users')


This generates the following, which is what I want:



<Users>
<user id="QALoadTest1" action="add" actionTime="2015-11-01T00:00:00" firstName="Load" lastName="Test1" email="QALoadTest1@loadtest.com">
<identifier isActive="1" actionTime="2015-11-01T00:00:00" displayName="Load Test 1">11111111111</identifierId>
</user>
</Users>


What's the difference between using a column value and a string here? Interestingly, when I wrap the column value in single quotes (''' + identifierId + ''') it generates the XML in the correct structure, but with the additional quote marks (...displayName="Load Test 1">'11111111111'</identifierId>)

0 comments:

Post a Comment