SQL server query xml attribute closing on each field

Question

I need to write a Sql server query to generate exactly this xml file.

<Ob name="MData">
  <FieldList>
    <Field name="ID">0980</Field>
    <Field name="IDDes">Working</Field>
    <Field name="Category">Arts Student</Field>
  </FieldList>
</Ob>

My table looks like this:

CREATE TABLE [dbo].[MData](
  [ID] [nvarchar](50) NULL, 
  [IDDes] [nvarchar](50) NULL, 
  [Category] [nvarchar](50) NULL)

This is what I did so far, but I need some experts help to get the exact XML format.

Select ( 
    Select 
        'ID' as '@name',
        ID 
    from 
        dbo.MData as B 
    Where A.ID = B.ID 
    FOR XML Path('Field'), Type 
), ( 
    Select 
        'IDDes' as '@name' , 
        IDDes 
        From dbo.MData As B 
    Where A.ID = B.ID 
    FOR XML Path('Field'), Type 
), ( 
    Select 
        'Category' as '@name' , 
        category 
    From dbo.MData As B 
    Where A.ID = B.ID 
    FOR XML Path('Field'), Type 
) 
FROM dbo.MData As A 
FOR XML path('FieldList'), Root('OB')

Stack Overflow is not a "give me the codes" site; we expect you to put some effort in first, and show what you have tried. Furthermore, you make no mention of the table schemas, although I can guess what it is. — LittleBobbyTables - Au Revoir, Jul 14 '13 at 15:57
What base table do you want to get your data from? Please show us the table structure and sample data! — marc_s, Jul 14 '13 at 15:58
CREATE TABLE [dbo].[MData]( [ID] [nvarchar](50) NULL, [IDDes] [nvarchar](50) NULL, [Category] [nvarchar](50) NULL ) ON [PRIMARY] — Muhammad Arif, Jul 14 '13 at 16:47
ID IDDes Category 0980 Working Arts Student 0981 Working Arts Student 070 Not Working Arts Student — Muhammad Arif, Jul 14 '13 at 16:50
Select ( Select 'ID' as '@name',ID from dbo.MData as B Where A.ID = B.ID FOR XML Path('Field'), Type ), ( Select 'IDDes:' as '@name' , IDDes From dbo.MData As B Where A.ID = B.ID FOR XML Path('Field'), Type ), ( Select 'Category:' as '@name' , category From dbo.MData As B Where A.ID = B.ID FOR XML Path('Field'), Type ) FROM dbo.MData As A FOR XML path('FieldList'), Root('OB') — Muhammad Arif, Jul 14 '13 at 17:07
This is what I did so far I need some expert help to get the exact XML format. Thanks in advance. — Muhammad Arif, Jul 14 '13 at 17:11

score 1 · Accepted Answer · answered Jul 14 '13 at 19:01

This is your select...

Select ( 
    Select 
        'ID' as '@name',
        ID 
    from 
        dbo.MData as B 
    Where A.ID = B.ID 
    FOR XML Path('Field'), Type 
), ( 
    Select 
        'IDDes' as '@name' , 
        IDDes 
        From dbo.MData As B 
    Where A.ID = B.ID 
    FOR XML Path('Field'), Type 
), ( 
    Select 
        'Category' as '@name' , 
        category 
    From dbo.MData As B 
    Where A.ID = B.ID 
    FOR XML Path('Field'), Type 
) 
FROM dbo.MData As A 
FOR XML path('FieldList'), Root('OB')

Wrap each of your values with (SELECT your_value).

Like this:

Select ( 
    Select 
        'ID' as '@name',
        (SELECT ID)
    from 
        dbo.MData as B 
    Where A.ID = B.ID 
    FOR XML Path('Field'), Type 
), ( 
    Select 
        'IDDes' as '@name' , 
        (SELECT IDDes)
        From dbo.MData As B 
    Where A.ID = B.ID 
    FOR XML Path('Field'), Type 
), ( 
    Select 
        'Category' as '@name' , 
        (SELECT category)
    From dbo.MData As B 
    Where A.ID = B.ID 
    FOR XML Path('Field'), Type 
) 
FROM dbo.MData As A 
FOR XML path('FieldList'), Root('OB')

Dear Ozren, thank you so much from the bottom of my heart. This is what I was looking for. Thanks again. — Muhammad Arif, Jul 14 '13 at 22:38

SQL server query xml attribute closing on each field

1 Answers1