2

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.

3
  • Which DBMS? SQL Server I suppose? Try FOR XML PATH (''), TYPE, ROOT ('Units'). Commented May 15, 2018 at 12:42
  • thanks a lot it works. Commented May 15, 2018 at 13:02
  • @stickybit In this case I'd prefer to combine AS [*] with PATH('UNIT')... Commented May 15, 2018 at 16:59

1 Answer 1

3

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!

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.