I am using this query,
SELECT HEAD.ID AS "HEAD/@ID",
HEAD.CODE AS "HEAD/CODE",
CONVERT(VARCHAR,HEAD.DATE,112) AS "HEAD/DATE",
HEAD.DOCNO AS "HEAD/DOCNO",
ITEM.SERIAL AS "HEAD/ITEM/@ID",
ITEM.ITEMNO AS "HEAD/ITEM/ITEMNO",
ITEM.UOM AS "HEAD/ITEM/UOM",
ITEM.QTY AS "HEAD/ITEM/QTY"
FROM PRODUCT_HDR HEAD, PRODUCT_DTL ITEM
WHERE HEAD.CODE = ITEM.CODE AND HEAD.DATE = ITEM.DATE
FOR XML PATH ('IDOC'), ELEMENTS;
to get result in below format, but its not working.
="1.0"="utf-8"
<HEAD ID="1">
<CODE>001</CODE>
<DATE>20141208</DATE>
<DOCNO>2001500000001</DOCNO>
<ITEM ID="1">
<ITEMNO>10001</ITEMNO>
<UOM>KG</UOM>
<QTY>10.000</QTY>
</ITEM>
<ITEM ID="2">
<ITEMNO>10002</ITEMNO>
<UOM>KG</UOM>
<QTY>20.000</QTY>
</ITEM>
<ITEM ID="3">
<ITEMNO>10003</ITEMNO>
<UOM>KG</UOM>
<QTY>30.000</QTY>
</ITEM>
</HEAD>