Get nested XML output sql server

Question

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>&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?

GriGrim · Accepted Answer · 2013-09-13 12:08:04Z

13

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')

answered Sep 13, 2013 at 12:08

GriGrim

2,9411 gold badge25 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

FrenkyB Over a year ago

It works! One thing is not clear to me: if I don't use type on the outer query - for xml path('Person'), /*no type here*/, Root('Root'), the output is the same as if I use the type keyword. Why is that? What is the main purpose of type keyword?

GriGrim Over a year ago

The output is the same but without type directive it is text, not xml: technet.microsoft.com/en-us/library/ms190025(v=sql.105).aspx

GriGrim Over a year ago

See this example: select (select 1 'c' for xml path('a')/*, type*/, root('b') ) + 'zzz'

Collectives™ on Stack Overflow

Get nested XML output sql server

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related