xml file data imported to sql with script

Question

im having this kind of xml:

<?xml version="1.0"?>
-<recordedData>
    <machine>ZSK40-2</machine>
    <date>2013/09/21</date>
    <hour>05:32</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>0</Value>
        </variable>-<variable>
            <Name>KGH_ALL_REAL</Name>
            <Value>0</Value>
        </variable>-<variable>
            <Name>KGH_F1_SET</Name>
            <Value>0</Value>
        </variable>-<variable>
            <Name>KGH_F1_REAL</Name>
            <Value>0</Value>
        </variable>-<variable>
            <Name>K_F1</Name>
            <Value>43</Value>
        </variable>-<variable>
            <Name>SCREW_RPM_SET</Name>
            <Value>550</Value>
        </variable>-<variable>
            <Name>SCREW_RPM_REAL</Name>
            <Value>550.085388183594</Value>
        </variable>-<variable>
            <Name>TORQUE</Name>
            <Value>1.21340000629425</Value>
        </variable>-<variable>
            <Name>CURRENT</Name>
            <Value>60.1959991455078</Value>
        </variable>-<variable>
            <Name>KW_KG</Name>
            <Value>0</Value>
        </variable>-<variable>
            <Name>KW</Name>
            <Value>-0.990000009536743</Value>
        </variable>-<variable>
            <Name>MELT_PRESSURE</Name>
            <Value>0</Value>
        </variable>-<variable>
            <Name>MELT_TEMPERATURE</Name>
            <Value>214</Value>
        </variable>-<variable>
            <Name>PV1</Name>
            <Value>216</Value>
        </variable>-<variable>
            <Name>SP1</Name>
            <Value>210</Value>
        </variable>-<variable>
            <Name>PV2</Name>
            <Value>239</Value>
        </variable>-<variable>
            <Name>SP2</Name>
            <Value>220</Value>
        </variable>-<variable>
            <Name>PV3</Name>
            <Value>220</Value>
        </variable>-<variable>
            <Name>SP3</Name>
            <Value>220</Value>
        </variable>-<variable>
            <Name>PV4</Name>
            <Value>220</Value>
        </variable>-<variable>
            <Name>SP4</Name>
            <Value>220</Value>
        </variable>-<variable>
            <Name>PV5</Name>
            <Value>209</Value>
        </variable>-<variable>
            <Name>SP5</Name>
            <Value>210</Value>
        </variable>-<variable>
            <Name>PV6</Name>
            <Value>210</Value>
        </variable>-<variable>
            <Name>SP6</Name>
            <Value>210</Value>
        </variable>-<variable>
            <Name>PV7</Name>
            <Value>210</Value>
        </variable>-<variable>
            <Name>SP7</Name>
            <Value>210</Value>
        </variable>-<variable>
            <Name>PV8</Name>
            <Value>210</Value>
        </variable>-<variable>
            <Name>SP8</Name>
            <Value>210</Value>
        </variable>-<variable>
            <Name>PV9</Name>
            <Value>210</Value>
        </variable>-<variable>
            <Name>SP9</Name>
            <Value>210</Value>
        </variable>-<variable>
            <Name>PV10</Name>
            <Value>210</Value>
        </variable>-<variable>
            <Name>SP10</Name>
            <Value>210</Value>
        </variable>-<variable>
            <Name>PV11</Name>
            <Value>220</Value>
        </variable>-<variable>
            <Name>SP11</Name>
            <Value>220</Value>
        </variable>
    </CollectedData>
</recordedData>

Can anyone provide a sample sql script for extracting all the data from it please.

i would really apreciate this since im new to xml.

Thanks in advance.

if there is an easier way to paste the xml data please tell me. — user2725348
– user2725348, Commented Sep 23, 2013 at 13:26

marc_s · Accepted Answer · 2013-09-23 13:46:57Z

1

If you have your data in a table already, you can use something like this:

DECLARE @Tmp TABLE (ID INT NOT NULL, XmlContent XML)

INSERT INTO @TMP VALUES(1, '......(your entire XML here).......)

SELECT
    ID,
    MACHINE = XmlContent.value('(/recordedData/machine)[1]', 'varchar(50)'),
    RecordingDate = XmlContent.value('(/recordedData/date)[1]', 'varchar(50)'),
    RecordingTime = XmlContent.value('(/recordedData/hour)[1]', 'varchar(50)'),
    VariableName = XVar.value('(Name)[1]', 'varchar(50)'),
    VariableValue = XVar.value('(Value)[1]', 'varchar(50)')
FROM
    @Tmp
CROSS APPLY 
    XmlContent.nodes('/recordedData/CollectedData/variable') AS XTbl(XVar)

This gives you an output something like:

enter image description here

.... and so on - listing all the variables with their name and value.

answered Sep 23, 2013 at 13:46

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2725348 Over a year ago

is there also a way to swap the columns to rows and the rows to columns? I mean column names should be VariableName and VariableValue should be the values of the table. Also the first record to be the date/time ( combined from RecordingDate and RecordingTime ) ?

user2725348 Over a year ago

what you did up there is wonderfull anyway, thank's a million! should get me a good understanding on how to treat xml's.

Collectives™ on Stack Overflow

xml file data imported to sql with script

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related