sql xml with nested node

Question

I have a below table in MS sql server 2012

 emp_ID     emp_dept
 -2          HR
  3          CS

I need the below xml format :

<row>
  <staticData>
  <Field fld="emp_ID" value="-2" />
  <Field fld="emp_ID" value="3" /> 
  </staticData> 
 <DynData>
  <Field fld="emp_dept" value="HR" />
  <Field fld="emp_dept" value="CS" />
  </DynData> 
</row>

I have the sample query but it is not working for multiple columns :

  SELECT top 5 
 'emp_ID'  AS [Field/@fld],  
 emp_ID  AS [Field/@value]   
 FROM Employee
 FOR XML PATH ('staticData'), ROOT('row')

AlwaysLearning · Accepted Answer · 2020-03-17 13:21:00Z

2

SQL XML generation is a bit weird. If you need to have two elements of the same name following each other you need to separate them with a null valued column, e.g.:

select top 5 
  'emp_ID' as [Field/@fld],  
  emp_ID as [Field/@value],
  null, --<<-- TRICK
  'emp_dept' as [Field/@fld],  
  emp_dept as [Field/@value]
from (values
  (-2, N'HR'),
  (3, N'CS')
) Employee (emp_ID, emp_dept)
for xml path('staticData'), root('row')

Which yields...

<row>
    <staticData>
        <Field fld="emp_ID" value="-2"/>
        <Field fld="emp_dept" value="HR"/>
    </staticData>
    <staticData>
        <Field fld="emp_ID" value="3"/>
        <Field fld="emp_dept" value="CS"/>
    </staticData>
</row>

=====

Second answer to match the second version of the question...

I'd be querying your designer about the requirements of the new XML. There's now no relationship between the elements containing emp_ID and emp_dept attributes. Previously they were siblings grouped together inside the staticData parent elements, now they are in separate parents and can be ordered independently according to the whims of SQL Server.

To generate your new XML format requires subqueries to populate the children of the staticData and DynData elements, e.g.:

create table dbo.Employee (
  emp_ID int not null,
  emp_dept nvarchar(2) not null
);

insert dbo.Employee (emp_ID, emp_dept) values
  (-2, N'HR'),
  (3, N'CS');

select
  (
    select
      'emp_ID' as [@fld],  
      emp_ID as [@value]
    from Employee
    for xml path('Field'), type
  ) as [staticData],
  (
    select
      'emp_dept' as [@fld],  
      emp_dept as [@value]
    from Employee
    for xml path('Field'), type
  ) as [DynData]
for xml path('row');

Which yields...

<row>
    <staticData>
        <Field fld="emp_ID" value="-2"/>
        <Field fld="emp_ID" value="3"/>
    </staticData>
    <DynData>
        <Field fld="emp_dept" value="HR"/>
        <Field fld="emp_dept" value="CS"/>
    </DynData>
</row>

edited Mar 17, 2020 at 13:21

answered Mar 17, 2020 at 10:28

AlwaysLearning

8,9425 gold badges32 silver badges40 bronze badges

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

3 Comments

user1482689 Over a year ago

Small changes required on top of the above solution,I need final xml in as below format any suggestion, <row> <staticData> <Field fld="emp_ID" value="-2"/> </staticData> <DynData> <Field fld="emp_dept" value="CS"/> </DynData> </row>

AlwaysLearning Over a year ago

Could you ask a new question? It seems like you're mixing emp_ID and emp_dept from two separate rows, based on your example table data anyways.

user1482689 Over a year ago

I have modified the xml, could u please check.

Collectives™ on Stack Overflow

sql xml with nested node

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related