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

Question

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

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:

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:

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

Can you provide the source data as text? It's easier to write a query if we can copy/paste. Or better yet, provide the source data as temp table/table var or SQL Fiddle. That gives us something to write the query against. — David Rushton, Dec 23 '16 at 12:00

score 4 · Accepted Answer · answered Dec 23 '16 at 12:26

4

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>

answered Dec 23 '16 at 12:26

Mikael Eriksson

136,425
22
210
281

Upvoted, just needs the values to be padded with leading zeroes to get **exactly** to output OP was asking for. – 3N1GM4 Dec 23 '16 at 12:39
@3N1GM4 Not sure about that since there is nothing in the query by OP that takes care of that and still the result is padded. – Mikael Eriksson Dec 23 '16 at 12:43
Yeah, fair enough, there's some conflict between the screenshots of data and the output from the failed solution so it's not clear whether the values in the database are actually `INT` or some `VARCHAR` type with the leading zeroes already prefixed on - no biggie. – 3N1GM4 Dec 23 '16 at 12:49
@3N1GM4 Just in case it is needed. [Pad a string with leading zeros so it's 3 characters long in SQL Server 2008](http://stackoverflow.com/questions/16760900/pad-a-string-with-leading-zeros-so-its-3-characters-long-in-sql-server-2008) – Mikael Eriksson Dec 23 '16 at 12:50
I hope you don't mind if I ask a question about your solution (which I found very helpful myself) - why is the `TYPE` directive required on the `FOR XML PATH` in the subquery, but not on the instance on the final line? I see that if I remove this from the subquery, I start seeing `<` and `>` instead of `<` and `>` around the tags from the subquery, but can't understand why tags like `` (which are outside of the subquery) do not suffer the same issue, despite the `TYPE` directive not being present on the main query's `FOR XML PATH` statement? – 3N1GM4 Dec 23 '16 at 12:52
TYPE make sql server return the data as the XML data type. Otherwise it will be returned as nvarchar(max). So leaving out TYPE in the subquery will return the created XML as a string value and then when the outer query adds that string as an element value to the outer XML it has to be entitized to still be valid in XML. & -> & and > -> > etc. As for the difference in using TYPE or not on the outer query it depends on the consumer of the result. SSMS does not care what you do there and presents the result as XML in both cases. @3N1GM4 – Mikael Eriksson Dec 23 '16 at 13:01
1

Fantastic, thanks for the explanation! Would upvote twice if I could. ;) – 3N1GM4 Dec 23 '16 at 13:03

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

1 Answers1