CODEWITHSUNDEEP
sqlsql-serverxml
user1591668
user1591668

Reputation: 2893

Turn my mssql query into a structured xml result set

Right now I have a simple query that turns my sql results into XML however it does not do it in the way I want it to . This is my query

 select top 4 id,email from [user] where len(email) > 5

 for xml raw, ROOT('MyRoot')

and this is the result below

<MyRoot>
<row id="7" email="[email protected]" />
<row id="8" email="[email protected]" />
<row id="9" email="[email protected]" />
<row id="10" email="[email protected]" />
</MyRoot>

I would like to change that result set into this

   <MyRoot>
   <row> <id>7</id> <email>[email protected]</email> </row>
   <row  <id>8</id> <email>[email protected]</email> </row>
   <row  <id>9</id> <email>[email protected]</email> </row>
   <row <id>10</id> <email>[email protected]</email> </row>
   </MyRoot>

any help would be appreciated . I've looked through here and could not find anything since I am new at this .

Upvotes: 0

Views: 31

Answers (1)

lptr
lptr

Reputation: 6788

select top 4 id,email from [user] where len(email) > 5
for xml raw, elements, ROOT('MyRoot') --columns as elements instead of attributes(default)

Upvotes: 3

Related Questions