TSQL FOR XML Formatting

I have a table that I would like to convert into an XML format. The table is called tempTable and holds data as such:

REF     DESC     QTY
100001  Normal   1
100002  OOH      1

I need to create the XML in this given format:

<row>
  <LIN NUM="1">
    <FLD NAME="REF">100001</FLD>
    <FLD NAME="DES">Normal</FLD>
    <FLD NAME="QTY">1</FLD>
  </LIN>
  <LIN NUM="2">
    <FLD NAME="REF">100002</FLD>
    <FLD NAME="DES">OOH</FLD>
    <FLD NAME="QTY">1</FLD>
  </LIN>
</row>

I have tried the below code:

SET @line = (SELECT '1' AS '@NUM', REF AS 'REF', DES AS 'DES', QTY AS 'QTY' FROM tempTable WHERE ORDER= @ORDER
FOR XML PATH('LIN'))

SELECT @line
FOR XML PATH

However this produces:

<row>
  <LIN NUM="1">
    <REF>100001</REF>
    <DES>Normal</DES>
    <QTY>1</QTY>
  </LIN>
  <LIN NUM="1">
    <REF>100002</REF>
    <DES>OOH</DES>
    <QTY>1</QTY>
  </LIN>
</row>

Does anyone know how I can:

A) Change the 'LIN' 'NUM' attribute to be incremental based on the number of records for that order?
B) Add the 'Name' attribute to the 'LIN' details and change the field names to 'FLD'. When I try to change the name to 'FLD' it concats the values onto a single node, as such:
```
<row>
  <LIN NUM="1">
    <FLD>100001Normal1</FLD>
  </LIN>
  <LIN NUM="1">
    <FLD>100002OOH1</FLD>
  </LIN>
</row>
```

I assume if I can add the 'NAME' attribute that would separate out the values?

Any advice/help would be greatly appreciated.

Thanks

Solution

declare @tempTable table (Ref int, Des varchar(100), Qty int)
insert into @tempTable values (100001, 'Normal', 1), (100002, 'OOH', 1)

SELECT ROW_NUMBER() OVER (ORDER BY Ref) AS '@NUM',
     'REF' AS 'FLD/@NAME', REF AS 'FLD', '',
     'DES' AS 'FLD/@NAME', DES AS 'FLD', '',
     'QTY' AS 'FLD/@NAME', QTY AS 'FLD'

FROM @tempTable 
FOR XML PATH('LIN'), ROOT ('row')

produces:

<row>
  <LIN NUM="1">
    <FLD NAME="REF">100001</FLD>
    <FLD NAME="DES">Normal</FLD>
    <FLD NAME="QTY">1</FLD>
  </LIN>
  <LIN NUM="2">
    <FLD NAME="REF">100002</FLD>
    <FLD NAME="DES">OOH</FLD>
    <FLD NAME="QTY">1</FLD>
  </LIN>
</row>

with a little help from https://stackoverflow.com/a/25412657/4473405 because without the empty strings between the node and the attribute in the select, SQL Server gives an error:

Attribute-centric column 'FLD/@NAME' must not come after a non-attribute-centric sibling in XML hierarchy in FOR XML PATH.

EDIT: as has been mentioned in the comments, this post explains why the empty quote trick works.

To summarize:

in FOR XML PATH, columns without a name become text nodes
- NULL or '' therefore become empty text nodes
- you can convert a named column to an unnamed one by using AS *
- this helps to separate the previously output nodes from the next one, so that SQL Server knows e.g. to start a new element for the next column. Otherwise, it gets confused when for example the attribute already exists on what it thinks is the "current" element