1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59
| DECLARE @Xml XML = '<Elements>
<Element>
<NumeroElement>476</NumeroElement>
<Info1>358</Info1>
<Info2>4012</Info2>
<Code1>
<Code>OK</Code>
<Date1>2017.06.30 16:45</Date1>
</Code1>
<Code2>
<Code>OK</Code>
<Date1>2017.06.30 12:40</Date1>
</Code2>
<Code3>
<STATUT>XX</STATUT>
<Date2>2017.06.22 17:12</Date2>
<Date3>2017.06.30 12:40</Date3>
<LOT>Lot1</LOT>
</Code3>
</Element>
<Element>
<NumeroElement>542</NumeroElement>
<Info1>87</Info1>
<Info2>885</Info2>
<Code1>
<Code>OK</Code>
<Date1>2017.06.31 10:40</Date1>
</Code1>
<Code2>
<Code>OK</Code>
<Date1>2017.06.30 12:40</Date1>
</Code2>
<Code3>
<STATUT>YY</STATUT>
<Date2>2017.06.22 17:12</Date2>
<Date3>2017.06.30 12:40</Date3>
<LOT>Lot2</LOT>
</Code3>
</Element>
</Elements>'
INSERT INTO table_element(NumeroElement, Info1, Info2, Code_1, Date1_1, Code_2, Date1_2, Statut_3, Date2_3, Date3_3, Lot_3)
SELECT
X.t.value('(NumeroElement/text())[1]', 'INT') AS numero_element,
X.t.value('(Info1/text())[1]', 'INT') AS info1,
X.t.value('(Info2/text())[1]', 'INT') AS info2,
X.t.value('(Code1/Code/text())[1]', 'VARCHAR(50)') AS code_1,
X.t.value('(Code1/Date1/text())[1]', 'VARCHAR(50)') AS date1_1,
X.t.value('(Code2/Code/text())[1]', 'VARCHAR(50)') AS code_2,
X.t.value('(Code2/Date1/text())[1]', 'VARCHAR(50)') AS date1_2,
X.t.value('(Code3/STATUT/text())[1]', 'VARCHAR(50)') AS statut_3,
X.t.value('(Code3/Date2/text())[1]', 'VARCHAR(50)') AS date2_3,
X.t.value('(Code3/Date3/text())[1]', 'VARCHAR(50)') AS date3_3,
X.t.value('(Code3/LOT/text())[1]', 'VARCHAR(50)') AS lot_3
FROM @Xml.nodes('Elements/Element') AS X(t) |
Partager