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')

Answer 1

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')