Get nested XML output sql server

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:

  • Issue with XML Modify
  • TSQL - Looking for code clarification
  • SQL Server 2008 - ignoring null values
  • What does ApplicationIntent=ReadOnly mean in the connection string
  • SSIS package hangs while running
  • T-SQL, XQuery Invalid Column
  • <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>&lt;Address&gt;&lt;city&gt;Oklahoma&lt;/city&gt;&lt;street&gt; Cowley Road&lt;/street&gt;&lt;streetno&gt;34&lt;/streetno&gt;&lt;/Address&gt;</Person>
      <Person id="2">
        <firstname>Sandy</firstname>
        <lastname>Mc Gee</lastname>&lt;Address&gt;&lt;city&gt;Anchorage&lt;/city&gt;&lt;street&gt; North Green Clarendon Road&lt;/street&gt;&lt;streetno&gt;29&lt;/streetno&gt;&lt;/Address&gt;</Person>
      <Person id="3">
    

    What I am doing wrong?

    One Solution collect form web for “Get nested XML output sql server”

    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')
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.