Geek Answers Handbook

TSQL for XML formatting

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

REF     DESC     QTY
100001  Normal   1
100002  OOH      1

I need to create XML in this 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 tried the code below:

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 gives:

<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 attribute 'LIN' 'NUM' so that it is incremental depending on the number of records for this order?

  • B) Add the “Name” attribute to the “LIN” data and change the field names in the “FLD” field. When I try to change the name to "FLD", it concatenates the values ​​to a single node, as such:

    <row>
  <LIN NUM="1">
    <FLD>100001Normal1</FLD>
  </LIN>
  <LIN NUM="1">
    <FLD>100002OOH1</FLD>
  </LIN>
</row>

I assume, can I add the attribute "NAME" that will highlight the values?

Any advice / help is appreciated.

thank

+4
2
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')

:

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

fooobar.com/questions/702905/..., node select SQL Server :

"FLD/@NAME" -- XML FOR XML PATH.

EDIT: , , .

:

  • FOR XML PATH,
    • NULL '',
    • AS *
    • , SQL Server , . . , , , , ""
+5
DECLARE @t TABLE (ref varchar(10), descr varchar(10), qty varchar(3))
insert into @t
values('100001', 'Normal', '1'),('10002','OOH','1')

SELECT ROW_NUMBER() OVER (ORDER BY a.ref) AS '@NUM',
(SELECT 'REF' AS 'FLD/@NAME'
 ,ref AS 'FLD'
FROM @t AS FLD
WHERE FLD.ref = a.ref
FOR XML PATH(''), TYPE),
(SELECT 'DESCR' AS 'FLD/@DESCR'
 ,descr AS 'FLD'
FROM @t AS FLD
WHERE FLD.ref = a.ref
FOR XML PATH(''), TYPE),
(SELECT 'QTY' AS 'FLD/@QTY'
 ,QTY AS 'FLD'
FROM @t AS FLD
WHERE FLD.ref = a.ref
FOR XML PATH(''), TYPE)
FROM @t AS a
FOR XML PATH('LIN'), ROOT('row')
0

More articles:


All Articles