SQL Server for Xml Path Make Repeating Nodes

SQL Server FOR XML Path make repeating nodes

Add a column with NULL as value to generate a separate item node for each column.

SELECT 
  t.col1 as 'item'
 ,NULL
 ,t.col2 as 'item'
 ,NULL
 ,t.col3 as 'item' 
FROM dbo.tbl as t 
FOR XML PATH('parent'), TYPE;

Result:

<parent>
  <item>1</item>
  <item>2</item>
  <item>3</item>
</parent>

SQL Fiddle

Why does this work?

Columns without a name are inserted as text nodes. In this case the NULL value is inserted as a text node between the item nodes.

If you add actual values instead of NULL you will see what is happening.

SELECT 
  t.col1 as 'item'
 ,'1'
 ,t.col2 as 'item'
 ,'2'
 ,t.col3 as 'item' 
FROM dbo.tbl as t 
FOR XML PATH('parent'), TYPE;

Result:

<parent>
  <item>1</item>1<item>2</item>2<item>3</item></parent>

Another way to specify a column without a name is to use the wildcard character * as a column alias.

Columns with a Name Specified as a Wildcard Character

It is not necessary to use the wildcard in this case because the columns with NULL values don't have a column name but it is useful when you want values from actual columns but you don't want the column name to be a node name.

T-SQL Query with XML PATH produces repeating parent nodes

You were almost there.

SQL

DECLARE @tb1 TABLE (id int);
insert into @tb1 values(100),(200);

DECLARE @tb2 table (id int, col2 int);
insert into @tb2 values(100,1),(100,2),(100,3),(200,3),(200,4);

SELECT 
  t.id as [@healthAccountRecord]
,(
  SELECT t2.col2 as [DiagnosisCode/@code]     
  FROM @tb2 as t2 
  WHERE t.id = t2.id    
  FOR XML PATH(''), TYPE, ROOT('DiagnosisCodes')
)
 FROM @tb1 as t
 FOR XML PATH('Account'), ROOT ('Accounts')

Output

<Accounts>
  <Account healthAccountRecord="100">
    <DiagnosisCodes>
      <DiagnosisCode code="1" />
      <DiagnosisCode code="2" />
      <DiagnosisCode code="3" />
    </DiagnosisCodes>
  </Account>
  <Account healthAccountRecord="200">
    <DiagnosisCodes>
      <DiagnosisCode code="3" />
      <DiagnosisCode code="4" />
    </DiagnosisCodes>
  </Account>
</Accounts>

SQL Server for xml path with double elements

This returns what you want:

DECLARE @table TABLE (
[type] VARCHAR(50)
, attribute VARCHAR(50)
)
INSERT INTO @table VALUES('FirstType','FirstAttribute')
                        ,('SecondType','SecondAttribute')

SELECT T1.type AS [type/type]
, T1.attribute AS [type/attribute]
FROM @table AS T1
FOR XML path('types');

But be warned: It is invalid due to the missing root node. SQL-Server's XML engine can deal with this, but other engines might fail...

I think, SQL Server is not so strict, as - very often - XML is built out of several fragments. And such a result could be one of these fragments without any problems...

Prevent repeating Elements using For XML Path in T-SQL output

Please try the following.

SQL

DECLARE @tbl TABLE ( 
     party_id NVARCHAR(8)
    ,data_timestamp datetime
    ,active_customer bit
    ,code  NVARCHAR(30)
    ,[description] NVARCHAR(20)
    ,date_of_birth date
    ,initials_np NVARCHAR(20)
    ,first_name NVARCHAR(20)
    ,last_name NVARCHAR(20)
    ,address_type NVARCHAR(20)
    ,address NVARCHAR(40)
    ,city NVARCHAR(10)
 );
 INSERT INTO @tbl
 (
      party_id 
    ,data_timestamp 
    ,active_customer
    ,code  
    ,[description] 
    ,date_of_birth 
    ,initials_np 
    ,first_name 
    ,last_name 
    ,address_type 
    ,address 
    ,city 
 )
VALUES
('CUST1',GETDATE(),1,'Customer Found','Valid', '11/05/1979', 'JBS','John','Smith','Primary Address','5 Cod street', 'Amsterdam'),
('CUST1',GETDATE(),1,'Customer Found','Valid', '11/05/1979', 'JBS','John','Smith','Secondary Address','4 Pike street', 'Rotterdam'),
('CUST2',GETDATE(),0,'Customer not Found',NULL, NULL, NULL,NULL,NULL,NULL,NULL, NULL);

;WITH rs AS
(
    SELECT party_id, data_timestamp 
        ,active_customer
        ,code  
        ,[description] 
        ,date_of_birth 
        ,initials_np 
        ,first_name 
        ,last_name 
    FROM @tbl
    GROUP BY party_id, data_timestamp 
        ,active_customer
        ,code  
        ,[description] 
        ,date_of_birth 
        ,initials_np 
        ,first_name 
        ,last_name 
)
SELECT data_timestamp as 'Metadata/data_timestamp'
    ,code as 'notification/code'
    ,[description] as   'notification/description'
    ,date_of_birth as 'register/natural_person/date_of_birth'
    ,initials_np as 'register/natural_person/name/Initials'
    ,first_name as   'register/natural_person/name/first_name'
    ,last_name as  'register/natural_person/name/last_name'
     ,(SELECT B.city, B.address_type ,
            B.address
            FROM  @tbl AS B
            WHERE B.party_id = T.party_id
            FOR XML PATH ('address'), TYPE
    ) 
FROM rs AS t
FOR XML PATH(''), ROOT('bdrp_client_response'), TYPE, ELEMENTS XSINIL;

SQL FOR XML to generate multiple same name nodes

You can select the names in a subquery

;WITH Temp(id, name1, name2)
AS
(
    SELECT 1, 'A', 'B' UNION
    SELECT 2, 'C', 'D' UNION
    SELECT 3, 'E', 'F'
)
SELECT
    id
    ,(SELECT name 
        FROM (
                SELECT name1 AS name 
                FROM Temp t2 
                WHERE t1.id = t2.id 
                UNION ALL 
                SELECT name2 AS name 
                FROM Temp t2 
                WHERE t1.id = t2.id) AS t 
        FOR XML PATH(''), TYPE) AS names
FROM
    Temp t1
FOR XML PATH('Data'), TYPE, ROOT('Feed')

SQL for XML Path using data values for node names and nesting

I think this may be what you need:

 select CAST('<'+ a.Category +'>'
 + CAST((SELECT
   ProductCode, ProductName
   FROM Thing b
   WHERE b.Category = a.Category
   FOR XML path('Product'), type) as VARCHAR(MAX)) 
 + '</' + a.Category + '>'  AS XML)
 from Thing a
 GROUP BY a.Category
 ORDER BY a.Category DESC
 for xml path(''), root('Products') 

Note that Thing is your table name and that you may need to add your REPLACE syntax back in. I was just going by the schema and data supplied.

XML path aggregation: one subquery for multiple `selects` on same subset

You can't quite do this without an extra subquery, but you can avoid querying the same table again and again.

All you need to do, is to get the data into a single XML blob in one subquery, then query it back out in each of the other subqueries:

select 
    f.firstname
    ,'My friend '+f.firstname+' owns a '+q1.collection_no_value as what_I_say_to_friends
    ,'My friend '+f.firstname+' owns a '+q2.collection_with_value as what_I_say_to_finance
from 
    friend f

    cross apply
    (
        select (
            select o.thing, o.price
            from owns o
            where o.firstname = f.firstname
            FOR XML PATH('row'), TYPE
        )
    ) x(XmlBlob)

    cross apply
    (
        select 
            stuff
            (
                ( 
                    select ' and a ' + x2.rw.value('(thing/text())[1]','nvarchar(max)')
                    from x.XmlBlob.nodes('/row') x2(rw)
                    FOR XML PATH(''), TYPE
                ).value('text()[1]','nvarchar(max)')
                ,1,7,''
            ) as collection_no_value
    ) as q1
    cross apply
    (
        select 
            stuff
            (
                ( 
                    select ' and a ' + x2.rw.value('(thing/text())[1]','nvarchar(max)') + ' priced ' + x2.rw.value('(price/text())[1]','nvarchar(max)')
                    from x.XmlBlob.nodes('/row') x2(rw)
                    FOR XML PATH(''), TYPE
                ).value('text()[1]','nvarchar(max)')
                ,1,7, ''
            ) as collection_with_value
    ) as q2

As you can see, it's actually more long-winded. On the other hand, if the subquery to create the blob is very complex then it may be more performant because the subquery is only executed once.

---

You can also achieve the same effect by creating a JSON array, in SQL Server 2016

select 
    f.firstname
    ,'My friend '+f.firstname+' owns a '+q1.collection_no_value as what_I_say_to_friends
    ,'My friend '+f.firstname+' owns a '+q2.collection_with_value as what_I_say_to_finance
from 
    friend f

    cross apply
    (
        select (
            select o.thing, o.price
            from owns o
            where o.firstname = f.firstname
            FOR JSON PATH
        )
    ) j(JsonBlob)

    cross apply
    (
        select 
            stuff
            (
                ( 
                    select ' and a ' + JSON_VALUE(j2.value, '$.thing')
                    from OPENJSON(j.JsonBlob) j2
                    FOR XML PATH(''), TYPE
                ).value('text()[1]','nvarchar(max)')
                ,1,7,''
            ) as collection_no_value
    ) as q1
    cross apply
    (
        select 
            stuff
            (
                ( 
                    select ' and a ' + JSON_VALUE(j2.value, '$.thing') + ' priced ' + JSON_VALUE(j2.value, '$.price')
                    from OPENJSON(j.JsonBlob) j2
                    FOR XML PATH(''), TYPE
                ).value('text()[1]','nvarchar(max)')
                ,1,7, ''
            ) as collection_with_value
    ) as q2

---

Obviously, in SQL Server 2017+ you can just use STRING_AGG:

select 
    f.firstname
    ,'My friend '+f.firstname+' owns a ' + STRING_AGG(CAST(o.thing AS nvarchar(max)), ' and a ') as what_I_say_to_friends
    ,'My friend '+f.firstname+' owns a ' + STRING_AGG(o.thing + ' priced ' + convert(nvarchar(max), o.price), ' and a ') as what_I_say_to_finance
from 
    friend f
group by f.firstname

---

Related Topics