I was hoping someone could provide a small example of sending a data table in an XML String into an SQL table. I've been searching this site and the internet but the examples I've found have different XML layouts. I'm not experienced enough to apply those examples to mine. Here is what the XML looks like, the data I want is at the end, in the "rs:data" section, note: this could be possible couple 1000 lines:
DECLARE @XML XML
SET @XML = '<xml xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
<x:PivotCache>
<x:CacheIndex>1</x:CacheIndex>
<s:Schema id="RowsetSchema">
<s:ElementType name="row" content="eltOnly">
<s:attribute type="Col1"/>
<s:attribute type="Col2"/>
<s:attribute type="Col3"/>
<s:attribute type="Col4"/>
<s:attribute type="Col5"/>
<s:attribute type="Col6"/>
<s:attribute type="Col7"/>
<s:attribute type="Col8"/>
<s:attribute type="Col9"/>
<s:extends type="rs:rowbase"/>
</s:ElementType>
<s:AttributeType name="Col1" rs:name=" Date">
<s:datatype dt:type="dateTime"/>
</s:AttributeType>
<s:AttributeType name="Col2" rs:name=" PlantId">
<s:datatype dt:maxLength="255"/>
</s:AttributeType>
<s:AttributeType name="Col3" rs:name=" Provider">
<s:datatype dt:maxLength="255"/>
</s:AttributeType>
<s:AttributeType name="Col4" rs:name=" Pipeline">
<s:datatype dt:maxLength="255"/>
</s:AttributeType>
<s:AttributeType name="Col5" rs:name=" Group">
<s:datatype dt:maxLength="255"/>
</s:AttributeType>
<s:AttributeType name="Col6" rs:name=" Type">
<s:datatype dt:maxLength="255"/>
</s:AttributeType>
<s:AttributeType name="Col7" rs:name=" Description">
<s:datatype dt:maxLength="255"/>
</s:AttributeType>
<s:AttributeType name="Col8" rs:name=" DataType">
<s:datatype dt:maxLength="255"/>
</s:AttributeType>
<s:AttributeType name="Col9" rs:name=" DataValue">
<s:datatype dt:type="float"/>
</s:AttributeType>
</s:Schema>
<rs:data>
<z:row Col1="2015-01-01T00:00:00" Col2="CH1" Col3="ENOGEX" Col4="ENOGEX" Col5="Fixed" Col6="Transport" Col7="Monthly Reservation" Col8="Cost" Col9="9483.1183870967743"/>
<z:row Col1="2015-01-02T00:00:00" Col2="CH1" Col3="ENOGEX" Col4="ENOGEX" Col5="Fixed" Col6="Transport" Col7="Monthly Reservation" Col8="Cost" Col9="9483.1183870967743"/>
<z:row Col1="2015-01-03T00:00:00" Col2="CH1" Col3="ENOGEX" Col4="ENOGEX" Col5="Fixed" Col6="Transport" Col7="Monthly Reservation" Col8="Cost" Col9="9483.1183870967743"/>
<z:row Col1="2015-01-04T00:00:00" Col2="CH1" Col3="ENOGEX" Col4="ENOGEX" Col5="Fixed" Col6="Transport" Col7="Monthly Reservation" Col8="Cost" Col9="9483.1183870967743"/>
<z:row Col1="2015-01-05T00:00:00" Col2="CH1" Col3="ENOGEX" Col4="ENOGEX" Col5="Fixed" Col6="Transport" Col7="Monthly Reservation" Col8="Cost" Col9="9483.1183870967743"/>
<z:row Col1="2015-01-06T00:00:00" Col2="CH1" Col3="ENOGEX" Col4="ENOGEX" Col5="Fixed" Col6="Transport" Col7="Monthly Reservation" Col8="Cost" Col9="9483.1183870967743"/>
</rs:data>
</x:PivotCache>
</xml>'
All the data i want is within the "rs:data" section. It's 9 columns by however many rows. Each row in the XML is started with "z:row". Here is what i've tried, this query executes without errors, showing my column headers, but no data:
DECLARE @TempTbl TABLE ([Date] datetime, PlantId varchar(50), Provider varchar(50), Pipeline varchar(50), [Group] varchar(50),
[Type] varchar(50), [Description] varchar(50), DataType varchar(50), DataValue float)
INSERT INTO @TempTbl
SELECT Tbl.Col.value('@Col1', 'datetime'),
Tbl.Col.value('@Col2', 'VARCHAR(50)'),
Tbl.Col.value('@Col3', 'VARCHAR(50)'),
Tbl.Col.value('@Col4', 'VARCHAR(50)'),
Tbl.Col.value('@Col5', 'VARCHAR(50)'),
Tbl.Col.value('@Col6', 'VARCHAR(50)'),
Tbl.Col.value('@Col7', 'VARCHAR(50)'),
Tbl.Col.value('@Col8', 'VARCHAR(50)'),
Tbl.Col.value('@Col9', 'float')
FROM @xml.nodes('//row') Tbl(Col)
--See the table
SELECT * FROM @TempTbl
Tiada ulasan:
Catat Ulasan