Закрытие xml-атрибута запроса SQL-сервера к каждому полю

Question

Мне нужно написать запрос к серверу Sql, чтобы создать именно этот XML-файл.

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

Моя таблица выглядит так:

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

Это то, что я сделал до сих пор, но мне нужны некоторые эксперты, чтобы получить точный формат XML.

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

Это ваш выбор...

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

Оберните каждое из ваших значений (SELECT your_value),

Как это:

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