CODEWITHSUNDEEP
sql-serverxmlt-sql
Sergеу Isupov
Sergеу Isupov

Reputation: 504

T-SQL generate XML

I have a table with following structure 

StreetNumber varchar(50)
Apartment varchar(50)
Floor varchar(50)

I want to convert the data to XML like this:

<Attributes>
    <AddressAttribute ID="1">
        <AddressAttributeValue>
            <Value>3</Value>
        </AddressAttributeValue>
    </AddressAttribute>
    <AddressAttribute ID="3">
        <AddressAttributeValue>
            <Value>1</Value>
        </AddressAttributeValue>
    </AddressAttribute>
    <AddressAttribute ID="2">
        <AddressAttributeValue>
            <Value>2</Value>
        </AddressAttributeValue>
    </AddressAttribute>
</Attributes>

Is there any way to convert each column to element with the same name and differs only with attribute ID?

Upvotes: 2

Views: 577

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67341

There is one accepted answer already, which is great. Just as an alternative...

Short explanation: Normally the engine would not allow to place several elements with the same name in the same level in one go. But the ,'' (empty elements) in between tell the engine to close one element and to start a new one. 

DECLARE @Addr TABLE(StreetNumber varchar(50),Apartment varchar(50),Floor varchar(50));
INSERT INTO @Addr VALUES('SomeStreet', 'App 1', 'Floor 2');

SELECT 1 AS [AddressAttribute/@ID]
      ,a.StreetNumber [AddressAttribute/AddressAttributeValue/Value]
      ,''
      ,2 AS [AddressAttribute/@ID]
      ,a.Apartment [AddressAttribute/AddressAttributeValue/Value]
      ,''
      ,3 AS [AddressAttribute/@ID]
      ,a.Floor AS [AddressAttribute/AddressAttributeValue/Value]
      ,''
FROM @Addr AS a      
FOR XML PATH(''),ROOT('Attributes');

The result

<Attributes>
  <AddressAttribute ID="1">
    <AddressAttributeValue>
      <Value>SomeStreet</Value>
    </AddressAttributeValue>
  </AddressAttribute>
  <AddressAttribute ID="2">
    <AddressAttributeValue>
      <Value>App 1</Value>
    </AddressAttributeValue>
  </AddressAttribute>
  <AddressAttribute ID="3">
    <AddressAttributeValue>
      <Value>Floor 2</Value>
    </AddressAttributeValue>
  </AddressAttribute>
</Attributes>

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

You can create the XML in a subquery in the column list where you unpivot the values in a table constructor, giving each column an ID.

Here is a sample that uses a table variable as the source of data. You should of course use your table instead. 

declare @T table
(
  StreetNumber varchar(50),
  Apartment varchar(50),
  Floor varchar(50)
);

insert into @T values('Street', 'Apartment', 'Floor');

select (
       select R.ID as '@ID',
              R.Value as 'AddressAttributeValue/Value'
       from (values(1, T.StreetNumber),
                   (2, T.Apartment),
                   (3, T.Floor)) as R(ID, Value)
       for xml path('AddressAttribute'), root ('Attributes'),  type
       )
from @T as T;

Result:

<Attributes>
  <AddressAttribute ID="1">
    <AddressAttributeValue>
      <Value>Street</Value>
    </AddressAttributeValue>
  </AddressAttribute>
  <AddressAttribute ID="2">
    <AddressAttributeValue>
      <Value>Apartment</Value>
    </AddressAttributeValue>
  </AddressAttribute>
  <AddressAttribute ID="3">
    <AddressAttributeValue>
      <Value>Floor</Value>
    </AddressAttributeValue>
  </AddressAttribute>
</Attributes>

Upvotes: 4

Related Questions