CODEWITHSUNDEEP
sqlsql-serversql-server-2012sql-server-2008-r2
Shyam Prasad
Shyam Prasad

Reputation: 1014

Creating XML from a SQL table - how to remove unwanted tag from XML structure?

I have a table tmpZstock with two columns: ZART_Article Code (itemcode) and GERNR_SerialNO (serialnumber):

enter image description here

CREATE TABLE tmpZstock (
    [ZART_Article Code] INT,
    GERNR_SerialNO INT
)

INSERT INTO tmpZstock ([ZART_Article Code], GERNR_SerialNO)
VALUES  (200078, 126),
        (200078, 127),
        (200078, 128),
        (200078, 129),
        (200078, 130),
        (200079, 131),
        (200079, 132),
        (200079, 133),
        (200079, 134),
        (200079, 135),
        (200079, 136),
        (200079, 137),
        (200079, 138),
        (200079, 139),
        (200079, 140),
        (200079, 141),
        (200080, 142),
        (200080, 143),
        (200080, 144),
        (200080, 145)

I need to create xml like this:

DesiredXML

But when I write this query:

Select [ZART_Article Code] as ITEMCODE ,
(
Select SERIALS.[GERNR_SerialNO]  as SERIALNO From tmpZstock SERIALS  
where SERIALS.[ZART_Article Code]=T1.[ZART_Article Code] FOR   XML rAW ('SERIALS'), TYPE )
From (select [ZART_Article Code] from tmpZstock group by [ZART_Article Code]) t1  FOR   XML PATH('SERIALNUMBERDETAILS'), TYPE
,Root ('SUMMARY')

the result is:

QueryResult

I don't need the <SERIALS> tag - how can I eliminate this tag from the result?

Upvotes: 2

Views: 103

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

You can use for xml path instead. Something like this should do it.

select T1.[ZART_Article Code] as ITEMCODE,
       (
       select T2.GERNR_SerialNO as SERIALNO
       from tmpZstock as T2
       where T1.[ZART_Article Code] = T2.[ZART_Article Code]
       for xml path(''), type
       )
from tmpZstock as T1
group by T1.[ZART_Article Code]
for xml path('SERIALNUMBERDETAILS'), root('SUMMARY')

Working sample with a table variable.

declare @T table
(
  [ZART_Article Code] int,
  GERNR_SerialNO  int
);

insert into @T values
(200078, 126),
(200078, 127),
(200078, 128),
(200078, 129),
(200078, 130),
(200079, 131),
(200079, 132),
(200079, 133),
(200079, 134),
(200079, 135),
(200079, 136),
(200079, 137),
(200079, 138),
(200079, 139),
(200079, 140),
(200079, 141),
(200080, 142),
(200080, 143),
(200080, 144),
(200080, 145);

select T1.[ZART_Article Code] as ITEMCODE,
       (
       select T2.GERNR_SerialNO as SERIALNO
       from @T as T2
       where T1.[ZART_Article Code] = T2.[ZART_Article Code]
       for xml path(''), type
       )
from @T as T1
group by T1.[ZART_Article Code]
for xml path('SERIALNUMBERDETAILS'), root('SUMMARY')

Result:

<SUMMARY>
  <SERIALNUMBERDETAILS>
    <ITEMCODE>200078</ITEMCODE>
    <SERIALNO>126</SERIALNO>
    <SERIALNO>127</SERIALNO>
    <SERIALNO>128</SERIALNO>
    <SERIALNO>129</SERIALNO>
    <SERIALNO>130</SERIALNO>
  </SERIALNUMBERDETAILS>
  <SERIALNUMBERDETAILS>
    <ITEMCODE>200079</ITEMCODE>
    <SERIALNO>131</SERIALNO>
    <SERIALNO>132</SERIALNO>
    <SERIALNO>133</SERIALNO>
    <SERIALNO>134</SERIALNO>
    <SERIALNO>135</SERIALNO>
    <SERIALNO>136</SERIALNO>
    <SERIALNO>137</SERIALNO>
    <SERIALNO>138</SERIALNO>
    <SERIALNO>139</SERIALNO>
    <SERIALNO>140</SERIALNO>
    <SERIALNO>141</SERIALNO>
  </SERIALNUMBERDETAILS>
  <SERIALNUMBERDETAILS>
    <ITEMCODE>200080</ITEMCODE>
    <SERIALNO>142</SERIALNO>
    <SERIALNO>143</SERIALNO>
    <SERIALNO>144</SERIALNO>
    <SERIALNO>145</SERIALNO>
  </SERIALNUMBERDETAILS>
</SUMMARY>

Upvotes: 4

Related Questions