CODEWITHSUNDEEP
sqlxmlt-sqltagsnodes
Oto
Oto

Reputation: 21

xml nodes within sql: how to force end daugther nodes

Is there any way to force end xml daugther nodes within sql? I need to end and reopen a specific node. My code currently looks like:

USE [DBNAME]
Select (Select 
        'EK' as 'prices/price/group',
        XUVP as 'prices/price/price',
        '1'  as 'prices/price/from',
        'beliebig' as 'prices/price/to',
        'FH' as 'prices/price/group',
        XHEK as 'prices/price/price',
        '1'  as 'prices/price/from',
        'beliebig' as 'prices/price/to',
        'D' as 'prices/price/group',
        XDEK as 'prices/price/price',
        '1'  as 'prices/price/from',
        'beliebig' as 'prices/price/to',
        'P' as 'prices/price/group',
        XPEK as 'prices/price/price',
        '1'  as 'prices/price/from',
        'beliebig' as 'prices/price/to'             
        FOR XML Path('article'),Type,Elements)      
FROM [dbo].[TABLENAME] FOR XML PATH ('articles'), Elements, ROOT('Root')
GO

which results in:

      <prices>
        <price>
          <group>EK</group>
          <price>459.000</price>
          <from>1</from>
          <to>beliebig</to>
  ---->   </price>
  ---->   <price> 
          <group>FH</group>
          <price>279.000</price>
          <from>1</from>
          <to>beliebig</to>            
        </price>
      </prices>

I need to 'force end' each price layer (second layer) after four subnotes and start a new price layer for the next for four nodes. The missing closing and opening tags are added and marked within the restult posted above.

Furthermore I want to apologize for any grammatical errors but I'm not a native. Even if this question might be an easy thing for someone who's used to sql, I'd really appreciate a helping hand here.

Thanks in advance and best wishes

Oto

Upvotes: 2

Views: 82

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81990

Not sure if this is the exact result you are looking for, but you can see by inserting a NULL, tags will reset

Declare @YourTable table (XUVP int,XHEK int,XDEK int,XPEK int)
Insert Into @YourTable values
(459,279,-999,999)

Select (Select 
        'EK' as 'price/group',
        XUVP as 'price/price',
        '1'  as 'price/from',
        'beliebig' as 'price/to',
        null,
        'FH' as 'price/group',
        XHEK as 'price/price',
        '1'  as 'price/from',
        'beliebig' as 'price/to',
        null,
        'D' as 'price/group',
        XDEK as 'price/price',
        '1'  as 'price/from',
        'beliebig' as 'price/to',
        null,
        'P' as 'price/group',
        XPEK as 'price/price',
        '1'  as 'price/from',
        'beliebig' as 'price/to'             
        FOR XML Path('prices'),Type,Elements, ROOT('article'))      
FROM @YourTable FOR XML PATH ('articles'), Elements, ROOT('Root')

Returns

<Root>
  <articles>
    <article>
      <prices>
        <price>
          <group>EK</group>
          <price>459</price>
          <from>1</from>
          <to>beliebig</to>
        </price>
        <price>
          <group>FH</group>
          <price>279</price>
          <from>1</from>
          <to>beliebig</to>
        </price>
        <price>
          <group>D</group>
          <price>-999</price>
          <from>1</from>
          <to>beliebig</to>
        </price>
        <price>
          <group>P</group>
          <price>999</price>
          <from>1</from>
          <to>beliebig</to>
        </price>
      </prices>
    </article>
  </articles>
</Root>

Upvotes: 1

Related Questions