CODEWITHSUNDEEP
xmlt-sql
Bruno Bieri
Bruno Bieri

Reputation: 10236

TSQL for xml xml-tag just once

I've the following SELECT Query:

SELECT
'2012' 'period',
Person.Name 'users/person'
FROM Person
FOR XML PATH(''), ROOT ('company')

this gives me the following XML:

<company>
  <period>2012</period>
  <users>
    <person>Dubach</person>
  </users>
  <period>2012</period>
  <users>
    <person>Pletscher</person>
  </users>
  <period>2012</period>
  ....

I would like to have the "period"-tag just once at the beginning of the result XML. how can I achieve this?

Upvotes: 1

Views: 269

Answers (3)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239754

You can nest the creation of the users/person elements inside a subselect, then create the outer XML around it:

declare @Person table (Name varchar(10))
insert into @Person values ('Bob'),('Harry')

SELECT
'2012' 'period',
(SELECT p.Name 'users/person'
FROM @Person p
FOR XML PATH(''), TYPE
)
FOR XML PATH(''), ROOT ('company')

Upvotes: 1

Asken
Asken

Reputation: 8061

You can use explicit mode to shape it anyway you like:

CREATE TABLE #person (
    name VARCHAR(10)
)

INSERT #person VALUES ('One')
INSERT #person VALUES ('Two')
INSERT #person VALUES ('Three')
INSERT #person VALUES ('Four')
INSERT #person VALUES ('Five')

SELECT
    1 AS Tag,
    NULL AS Parent,
    '2012' AS [company!1!period!ELEMENT],
    NULL AS [users!2!person!ELEMENT]
FROM
    (SELECT 1 AS fake) AS data
UNION ALL
SELECT
    2 AS Tag,
    1 AS Parent,
    '2012',
    P.name
FROM
    #person P
FOR XML EXPLICIT

Upvotes: 0

Oleg Dok
Oleg Dok

Reputation: 21766

DECLARE @period XML = '<period>2012</period>'
DECLARE @xml XML = (
        SELECT 
        Person.Name 'users/person'
        FROM Person
        FOR XML PATH(''), ROOT ('company')
    )

SET @xml.modify('insert sql:variable("@period") as first into (/company)[1]')

SELECT @xml

Upvotes: 3

Related Questions