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