how create xml from sql data in one section

Question

I have problem with create data in specify format. I need result like this:

<Units>
<Unit>0111024626914331</Unit>
<Unit>0111024626914348</Unit>
<Unit>00273129808000257576</Unit>
<Unit>00273129808000257590</Unit>
</Units>

my statement:

SELECT distinct PA.KOD_KRESKOWY as UNIT
        FROM LISTY_LINIOWE_POZ_P AS LLPP INNER JOIN PACZKI AS PA on PA.ID_PACZKI = LLPP.PACZKI_ID
        WHERE LISTY_LINIOWE_ID = 616665 
        FOR XML PATH ('UNITS'),type 

give me result:

   <Units>
     <UNIT>P/000005/2018+1/3</UNIT>
   </UNITS>
   <UNITS>
     <UNIT>P/000005/2018+2/3</UNIT>
   </UNITS>
   <UNITS>
     <UNIT>P/000005/2018+3/3</UNIT
   </Units>

I have Units in one column in table.

Answer 1

Although you've got you answer already it is worth to understand the levels of naming in FOR XML PATH().

Btw: My solution for you would be slightly different:

 SELECT PA.KOD_KRESKOWY as [*]
 FROM LISTY_LINIOWE_POZ_P AS LLPP INNER JOIN PACZKI AS PA on PA.ID_PACZKI = LLPP.PACZKI_ID
 WHERE LISTY_LINIOWE_ID = 616665 
 GROUP BY PA.KOD_KRESKOWY
 FOR XML PATH ('UNIT'),ROOT('UNITS'),type;

• GROUP BY is better than DISTINCT
• <UNIT> is more kinda row-tag. With only one column it works with the empty PATH('') too.

Try this out:

DECLARE @mockup TABLE(SomeValue VARCHAR(100), SomeNumber INT);
INSERT INTO @mockup 
VALUES('blah',1)
     ,('blub',2);

--Standard

SELECT SomeValue 
      ,SomeNumber
FROM @mockup 
FOR XML PATH('RowTag'),ROOT('RootTag')

<RootTag>
  <RowTag>
    <SomeValue>blah</SomeValue>
    <SomeNumber>1</SomeNumber>
  </RowTag>
  <RowTag>
    <SomeValue>blub</SomeValue>
    <SomeNumber>2</SomeNumber>
  </RowTag>
</RootTag>

--The first column will be a text() node below <RowTag>

SELECT SomeValue AS [*]
      ,SomeNumber
FROM @mockup 
FOR XML PATH('RowTag'),ROOT('RootTag')

<RootTag>
  <RowTag>blah<SomeNumber>1</SomeNumber></RowTag>
  <RowTag>blub<SomeNumber>2</SomeNumber></RowTag>
</RootTag>

--Both Columns have no name, funny effect...

SELECT SomeValue AS [*]
      ,SomeNumber AS [*]
FROM @mockup 
FOR XML PATH('RowTag'),ROOT('RootTag')

<RootTag>
  <RowTag>blah1</RowTag>
  <RowTag>blub2</RowTag>
</RootTag>

--Same Alias for both columns, might be also unexpected

SELECT SomeValue AS SameAlias
      ,SomeNumber AS SameAlias
FROM @mockup 
FOR XML PATH('RowTag'),ROOT('RootTag')

<RootTag>
  <RowTag>
    <SameAlias>blah1</SameAlias>
  </RowTag>
  <RowTag>
    <SameAlias>blub2</SameAlias>
  </RowTag>
</RootTag>

--empty <RowTag>

SELECT SomeValue 
      ,SomeNumber
FROM @mockup 
FOR XML PATH(''),ROOT('RootTag')

<RootTag>
  <SomeValue>blah</SomeValue>
  <SomeNumber>1</SomeNumber>
  <SomeValue>blub</SomeValue>
  <SomeNumber>2</SomeNumber>
</RootTag>

--no <RootTag>

SELECT SomeValue 
      ,SomeNumber
FROM @mockup 
FOR XML PATH('RowTag')

<RowTag>
  <SomeValue>blah</SomeValue>
  <SomeNumber>1</SomeNumber>
</RowTag>
<RowTag>
  <SomeValue>blub</SomeValue>
  <SomeNumber>2</SomeNumber>
</RowTag>

--No <RowTag> and no <RootTag>

SELECT SomeValue 
      ,SomeNumber
FROM @mockup 
FOR XML PATH('')

<SomeValue>blah</SomeValue>
<SomeNumber>1</SomeNumber>
<SomeValue>blub</SomeValue>
<SomeNumber>2</SomeNumber>

--No tags at all

SELECT SomeValue AS [*]
      ,SomeNumber AS [*]
FROM @mockup 
FOR XML PATH('')

blah1blub2 <-- but this is still native XML-type!

This is important to know, because sometimes you can achieve the impossible :-D

If you want, you might test this with deeper paths too (AS [Level1/Level2]) and/or with attributes (AS [Level1/Level2/@AnAttribute]). Have fun!