Hi All,
I have one xml as input IN SQL SERVER
i want to insert xml value into one sql table
my xml is ,
<main>
<Data>
<ID>CB08</Loca>
<Details>
<Itemcode>135</Itemcode>
<Item>test</Item>
</Details>
<Details>
<temcode>136</Itemcode>
<Item>test1</Item>
</Details>
</Data>
</main>
MY problem is,when i execute the above xml, it has been insert only first itemcode(first row from first <details>)but second row itemcode not inserting.
but i want to insert both rows into a tablemy query is ,
CREATE TABLE #temp
(
id VARCHAR(100),
ItemCode INT
)
INSERT #temp
SELECT
Data.value('(Location/text())[1]','VARCHAR(100)') AS Location,
Data.value('(Details/ItemCode/text())[1]','INT') AS ItemCode
FROM
@XML.nodes('/main/Data')AS AdviceData (Data)
So please let me know how can i insert both rows into one table.
THANKS IN ADVANCE.