Get nested XML output sql server
14,097
You forgot , type
and you don't need wxtra reading from table.
select tbl1.id '@id'
, tbl1.firstname
, tbl1.lastname
, (
select city
, street
, streetno
for xml path('Address'), type
)
from test_table as tbl1
for xml path('Person'), type, Root('Root')
Author by
FrenkyB
Updated on June 09, 2022Comments
-
FrenkyB almost 2 years
I have a problem with my xml output from sql server database. My table:
CREATE TABLE [dbo].[test_table]( [id] [int] IDENTITY(1,1) NOT NULL, [firstname] [nvarchar](255) NULL, [lastname] [nvarchar](255) NULL, [city] [nvarchar](255) NULL, [street] [nvarchar](255) NULL, [streetno] [int] NULL )
I want the output, where Address is nested inside each Person, like this:
<Root> <Person id="1"> <firstname>Stefanie</firstname> <lastname>Buckley</lastname> <Address> <city>Oklahoma</city> <street> Cowley Road</street> <streetno>34</streetno> </Address> </Person> <Person id="2"> <firstname>Sandy</firstname> <lastname>Mc Gee</lastname> <Address> <city>Montgomery</city> <street> Hague Parkway</street> <streetno>27</streetno> </Address> </Person> </Root>
I've tried with nested select like:
select tbl1.id '@id', tbl1.firstname, tbl1.lastname, ( select city,street,streetno from test_table as tbl2 where tbl2.id = tbl1.id for xml path('Address') ) from test_table as tbl1 for xml path('Person'), Root('Root')
but the output is like:
<Root> <Person id="1"> <firstname>Stefanie</firstname> <lastname>Buckley</lastname><Address><city>Oklahoma</city><street> Cowley Road</street><streetno>34</streetno></Address></Person> <Person id="2"> <firstname>Sandy</firstname> <lastname>Mc Gee</lastname><Address><city>Anchorage</city><street> North Green Clarendon Road</street><streetno>29</streetno></Address></Person> <Person id="3">
What I am doing wrong?