SQl to XML create node

Question

I have a flat table that I'm trying to turn into an xml output .

I need the address part to appear in a separate node.

Select ID,AddressLine1,PostCode,Price,Name from Property

I need this to appear like

<Property>
   <ID> 1 <ID>
   <Address> 
      <Line1>10 Downing Street</Line1>
      <PostCode>SW11SW</Postcode>
   </Address> 
   <Price> 1,000,000,000 <Price>
   <Name> My Next House<Name>
</Property>

any ideas how I achieve the address part?

Thanks Sp

Yaroslav · Accepted Answer · 2012-09-13 09:34:32Z

2

Supposing you are using MS SQL Server, using FOR XML PATH, try this code:

DECLARE @Property TABLE (ID INT, [AddressLine1] VARCHAR(30), PostCode VARCHAR(7),
                         Price MONEY, [Name] VARCHAR(20));
INSERT @Property
SELECT 1,'10 Downing Street','SW11SW',1000000000,'My Next House'

SELECT ID, AddressLine1 AS 'Address/AddressLine1', PostCode AS 'Address/Postcode',
       Price, Name 
  FROM @Property 
   FOR XML PATH('Property'), ELEMENTS

answered Sep 13, 2012 at 9:34

Yaroslav

6,55210 gold badges54 silver badges91 bronze badges

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

Comments

podiluska · Accepted Answer · 2012-09-13 09:30:01Z

0

In SQL Server? By using for xml path, elements

Select 
    ID,
    AddressLine1 as 'Address/Line1',
    PostCode as 'Address/Postcode',
    Price,
    Name 
from Property 
for xml path, elements

http://msdn.microsoft.com/en-us/library/ms189885

answered Sep 13, 2012 at 9:30

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Collectives™ on Stack Overflow

SQl to XML create node

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related