CODEWITHSUNDEEP
mysqlsql-server
Tom
Tom

Reputation: 8681

query not generating the desired xml output

I am trying to generate the following xml but it isn't getting generated as desired:

<partyIds>
   <id>2222</id>
   <id>2222</id>
   <id>2222</id>
</partyIds>

The xml generated by my query is as follows:

<partyIds>
   <companyId>105612</companyId>
</partyIds>
<partyIds>
   <companyId>106079</companyId>
</partyIds>

Query

SELECT DISTINCT top 1000 (mc.companyId)
FROM ciqMarketCap mc 
JOIN ciqCompany c 
       ON c.companyid = mc.companyid
WHERE c.companyStatusTypeId NOT IN (5,6) AND  c.companyTypeId IN (1,4) for xml path('partyIds')

Upvotes: 0

Views: 49

Answers (2)

Roger Wolf
Roger Wolf

Reputation: 7692

Here is an example of how you can combine column aliases with for xml attributes:

select top (3) ao.object_id as [id]
from sys.all_objects ao
for xml path(''), type, root('partyIds');

Alternatively, this syntax would also work:

select top (3) ao.object_id as [node()]
from sys.all_objects ao
for xml path('id'), type, root('partyIds');

Upvotes: 0

h.munawar
h.munawar

Reputation: 66

you need to define root.

for xml path(''), root ('partyIds')

Upvotes: 1

Related Questions