sql server - xml to sql - need a few more data from the xml -
while having xml data:
<machine>zsk40-2</machine> <date>2013/08/28</date> <hour>12:37</hour> <collecteddata> <variable> <name>product</name> <value>filler 580</value> </variable> <variable> <name>lot_number</name> <value>cg 00063 0</value> </variable> <variable> <name>shift_supervisor</name> <value> covaliu l</value> </variable> <variable> <name>kgh_all_set</name> <value>90</value> </variable> <variable> <name>kgh_all_real</name> <value>133.183883666992</value> </variable> <variable> <name>kgh_f1_set</name> <value>90</value> </variable> <variable> <name>kgh_f1_real</name> <value>133.183883666992</value> </variable> <variable> <name>k_f1</name> <value>33</value> </variable> <variable> <name>screw_rpm_set</name> <value>400</value> </variable> <variable> <name>screw_rpm_real</name> <value>399.452606201172</value> </variable> <variable> <name>torque</name> <value>19.6692142486572</value> </variable> <variable> <name>current</name> <value>71.0029983520508</value> </variable> <variable> <name>kw_kg</name> <value>0.0553370267152786</value> </variable> <variable> <name>kw</name> <value>7.36999988555908</value> </variable> <variable> <name>melt_pressure</name> <value>0</value> </variable> <variable> <name>melt_temperature</name> <value>140</value> </variable> <variable> <name>pv1</name> <value>141</value> </variable> <variable> <name>sp1</name> <value>140</value> </variable> <variable> <name>pv2</name> <value>160</value> </variable> <variable> <name>sp2</name> <value>160</value> </variable> <variable> <name>pv3</name> <value>160</value> </variable> <variable> <name>sp3</name> <value>160</value> </variable> <variable> <name>pv4</name> <value>160</value> </variable> <variable> <name>sp4</name> <value>160</value> </variable> <variable> <name>pv5</name> <value>160</value> </variable> <variable> <name>sp5</name> <value>160</value> </variable> <variable> <name>pv6</name> <value>150</value> </variable> <variable> <name>sp6</name> <value>150</value> </variable> <variable> <name>pv7</name> <value>150</value> </variable> <variable> <name>sp7</name> <value>150</value> </variable> <variable> <name>pv8</name> <value>154</value> </variable> <variable> <name>sp8</name> <value>150</value> </variable> <variable> <name>pv9</name> <value>150</value> </variable> <variable> <name>sp9</name> <value>150</value> </variable> <variable> <name>pv10</name> <value>160</value> </variable> <variable> <name>sp10</name> <value>160</value> </variable> <variable> <name>pv11</name> <value>180</value> </variable> <variable> <name>sp11</name> <value>180</value> </variable> </collecteddata> and script:
create table #zsk70( name varchar(25) , value varchar(30) ); insert #zsk70 (name, value) select x.variable.query('name').value('.', 'varchar(25)'), x.variable.query('value').value('.', 'varchar(30)') ( select cast(x xml) openrowset( bulk 'c:\20130828_1237_zsk40-2.xml', single_blob) t(x) ) t(x) cross apply x.nodes('collecteddata/variable') x(variable); select * #zsk70 drop table #zsk70 i data xml. how can machine , date + time in datetime row. in advance help! keep eye here.
try this:
select x.variable.query('name').value('.', 'varchar(25)'), x.variable.query('value').value('.', 'varchar(30)'), x2.variable.query('machine').value('.','varchar(30)'), x2.variable.query('date').value('.','datetime') ( select cast(x xml) openrowset( bulk 'c:\20130828_1237_zsk40-2.xml', single_blob) t(x) ) t(x) cross apply x.nodes('collecteddata/variable') x(variable) cross apply x.nodes('/') x2(variable); p.s. think reason downvotes on question left openrowset in query reference external file. makes bit of pain work example provided.
Comments
Post a Comment