CODEWITHSUNDEEP
sqlsql-serverxml
mohan111
mohan111

Reputation: 8865

How to format XML code when data is coming in one line

I have sample data 

DECLARE @Table1 TABLE (users int, ts int, name varchar(10), [1] int);

INSERT INTO @Table1 (users, ts, name, [1])
VALUES (1, 1, 'Raj', 0),
       (1, 3, 'maj', 2534),
       (1, 10, 'yes', 1458);

Script 

select
    'test' as 'job/branch/FBEN/opcode',
    users AS 'job/branch/FBEN/opcode',
    name as 'job/branch/FBEN/opcode' 
from 
    @Table1
where 
    ts = 1
for xml path('xmlexecute'), elements;

Output : 

<xmlexecute>
  <job>
    <branch>
      <FBEN>
        <opcode>test1Raj</opcode>
      </FBEN>
    </branch>
  </job>
</xmlexecute>

How can I get like this instead?

<xmlexecute>
  <job>
    <branch>
      <FBEN>
        <opcode>test1Raj</opcode>
        <opcode>1</opcode>
        <opcode>Raj</opcode>
      </FBEN>
    </branch>
  </job>
</xmlexecute>

If I'm trying to add 

select
    'test' as 'job/branch/FBEN/opcode',
    users AS 'job/branch/FBEN/opcode',
    name as 'job/branch/FBEN/opcode',
    [1] AS 'job/branch/FBEN/opcode/1'   
from 
    @Table1
where 
    ts = 3
for xml path('xmlexecute'), elements;

I get an error:

Msg 6850, Level 16, State 1, Line 14
Column name 'job/branch/FBEN/opcode/1' contains an invalid XML identifier as required by FOR XML; '1'(0x0031) is the first character at fault.

Upvotes: 2

Views: 376

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

Try it like this:

DECLARE @Table1 TABLE (users int, ts int, name varchar(10), [1] int);

INSERT INTO @Table1 (users, ts, name, [1])
VALUES (1, 1, 'Raj', 0),
       (1, 3, 'maj', 2534),
       (1, 10, 'yes', 1458);
select
(
    select
        'test' as [opcode]
        ,''
        ,users AS [opcode]
        ,''
        ,name as [opcode] 
        ,''
        ,[1] AS [opcode]
    from 
        @Table1
    where 
        ts = 1
    for xml path('FBEN'),ROOT('branch'),TYPE
) AS job
FOR XML PATH('xmlexecute')
;

The empty nodes ,'' between your opcode nodes tell the XML engine: This node is finished, start a new one. This is needed, if you want to place several nodes with the same name one below each other.

The result

<xmlexecute>
  <job>
    <branch>
      <FBEN>
        <opcode>test</opcode>
        <opcode>1</opcode>
        <opcode>Raj</opcode>
        <opcode>0</opcode>
      </FBEN>
    </branch>
  </job>
</xmlexecute>

Upvotes: 1

Related Questions