importing xml files through T-Sql

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

importing xml files through T-Sql

Author

Topic

tempus
Starting Member

47 Posts

Posted - 2013-10-02 : 01:53:39

i'm having a location on the server where each 10 minutes i receive 5-6 xml's with the same structure.
The name of each the file is different.

what i need to insert the xml's data into a table for storage and future reports. After inserting, each file must be deleted.

xml data looks like this ( 1 file ) :

<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>

the script to load 1 file and creating a custom data is this:

IF OBJECT_ID('tempdb..#tmpTable') IS NOT NULL
DROP TABLE #tmpTable

SELECT 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)')
Into #tmpTable
FROM (

SELECT CAST(XmlContent AS XML)
FROM OPENROWSET(
BULK 'C:\xml_path_here.xml',
SINGLE_BLOB) AS T(XmlContent)
) AS T(XmlContent)
CROSS APPLY
XmlContent.nodes('/recordedData/CollectedData/variable') AS XTbl(XVar)

DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.VariableName)
FROM #tmpTable c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT machine, RecordingDate, RecordingTime, ' + @cols + ' from
(
select machine
, RecordingDate
, RecordingTime
, VariableName
, VariableValue
from #tmpTable
) x
pivot
(
max(VariableValue)
for VariableName in (' + @cols + ')
) p '

execute(@query)
--SELECT * FROM #tmpTable
drop table #tmpTable

how can i modify this sql to get each file from the location , inserted , and the delete it?

Thanks in advance.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-02 : 09:44:55

You will need to use something that can access the operating system files. See this thread for one way to do it http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=188591 (look for the reply by a guy named James K). It uses xp_cmdshell to find the filename. Once you know the filename, you can import it and then use xp_cmdshell again to delete the file.

xp_cmdshell is disabled by default. Many organizations have a policy against enabling xp_cmdshell. So it may not work for you. If you do want to enable xp_cmdshell, use this:

exec sp_configure 'show advanced options', 1;
go

reconfigure;
go

exec sp_configure 'xp_cmdshell', 1
go

reconfigure;
Go

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-04 : 01:55:18

i would have done this using SSIS if available as it has a for each loop container which will automatically loop through these files and does data transfer. It also has a file system task which will perform file operations like file deletion. movement etc. It also doesnt require any code to be written to extract the data and just needs setting up mappings between source (xml file) and destination (sql server table).

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

tempus
Starting Member

47 Posts

Posted - 2013-10-04 : 02:38:25

I know that actually this are the 2 ways of doing this.

I have to say that I tried doing a SSIS package but i'm stuck at some point since I am new to this.

visakh16, I would like to do a SSIS package but I will need your assistance on the way.

On the other hand I have xp_cmdshell enabled. I have the script for one file and I don't know how to automatize the script for all files and then delete them.

Also with the full working script I can make a sp and then execute it with the agent every 10 minutes, this would also do the job.

this would be the script adjusted to my location for the xml files.

CREATE TABLE #tmp(filenames VARCHAR(64));

INSERT INTO #tmp
EXEC xp_cmdshell 'dir /B C:\tmp\* | findstr /I "xml"';

-- see the files that match the pattern.
SELECT * FROM #tmp;

-- test whether there are any files that match the pattern
IF EXISTS (SELECT * FROM #tmp)
SELECT 'Yes, there are files that match the pattern c:\tmp\*.xml';

DROP TABLE #tmp;

how to integrate it with the other one?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-04 : 08:17:53

quote:
Originally posted by tempus

I know that actually this are the 2 ways of doing this.

I have to say that I tried doing a SSIS package but i'm stuck at some point since I am new to this.

visakh16, I would like to do a SSIS package but I will need your assistance on the way.

On the other hand I have xp_cmdshell enabled. I have the script for one file and I don't know how to automatize the script for all files and then delete them.

Also with the full working script I can make a sp and then execute it with the agent every 10 minutes, this would also do the job.

this would be the script adjusted to my location for the xml files.

CREATE TABLE #tmp(filenames VARCHAR(64));

INSERT INTO #tmp
EXEC xp_cmdshell 'dir /B C:\tmp\* | findstr /I "xml"';

-- see the files that match the pattern.
SELECT * FROM #tmp;

-- test whether there are any files that match the pattern
IF EXISTS (SELECT * FROM #tmp)
SELECT 'Yes, there are files that match the pattern c:\tmp\*.xml';

DROP TABLE #tmp;

how to integrate it with the other one?

Your package will look like this

1. For Each Loop pointing to your server location
Set filetype as *.xml
Create a variable to retrieve the XML Source file name
2. Add a data flow task with XML SOurce and OLEDB destination.
Set an expression for XML source to point to variable containing filename. Set the database conection and table name inside OLEDB destination and map the columns. Run it to iterate through XML documents and transfer data to table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

tempus
Starting Member

47 Posts

Posted - 2013-10-07 : 03:15:25

I'm having troubles with the package. the xsd schema is a bit tricky and the data inserted is not formatted as I wanted to.

I would like to do it T-sql if it is possible. Also I do require additional help.

I will just put it in a stored proc and with the agent will run it every minute or so.

Thanks guys.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-07 : 06:05:30

quote:
Originally posted by tempus

I'm having troubles with the package. the xsd schema is a bit tricky and the data inserted is not formatted as I wanted to.

I would like to do it T-sql if it is possible. Also I do require additional help.

I will just put it in a stored proc and with the agent will run it every minute or so.

Thanks guys.

if you want to put it in t-sql you might need a loop to iterate through the files. you could still use SSIS for that part alone using for each loop and inside call a execute sql task to execute a procedure
Inside procedure you can fetch data from file using OPENROWSET BULK

see

http://technet.microsoft.com/en-us/library/ms191184.aspx

and then use XML methods like nodes(),value() etc to shred the data onto your table fields

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

tempus
Starting Member

47 Posts

Posted - 2013-10-07 : 07:13:21

visakh16,

I was thinking of doing it like this, since the xml's are coming continuously:

1. create a table name with the files - tmp1
2. insert the top 1 file (selected from the tmp1
3. delete the file inserted with the xpt_cmd_shell if possible
4. drop the table tmp1

run every minute as a stored proc with the server agent.

You think this could work?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-07 : 07:49:51

quote:
Originally posted by tempus

visakh16,

I was thinking of doing it like this, since the xml's are coming continuously:

1. create a table name with the files - tmp1
2. insert the top 1 file (selected from the tmp1
3. delete the file inserted with the xpt_cmd_shell if possible
4. drop the table tmp1

run every minute as a stored proc with the server agent.

You think this could work?

what do you mean by create table name with files?
do you mean making table name same as that of file?
insert the top 1 file ..how do you determine top 1 file here?

As I suggested using For Each loop will make sure iteration is done properly through files as it has a file enumerator. Otherwise you need .NET code for iterating through files by create an instance of File System Object

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

tempus
Starting Member

47 Posts

Posted - 2013-10-07 : 08:23:52

visakh16,

could you please drive me with a bit more information on how to do this?

1. I have created a new integration project.
2. I have inserted a for each loop container ( ive setup a connection and a location where the files are, and also *.xml for the Files )
3. I have inserted in the container an execute sql task

how to apply the main sql script to the sql task?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-08 : 00:17:12

quote:
Originally posted by tempus

visakh16,

could you please drive me with a bit more information on how to do this?

1. I have created a new integration project.
2. I have inserted a for each loop container ( ive setup a connection and a location where the files are, and also *.xml for the Files )
3. I have inserted in the container an execute sql task

how to apply the main sql script to the sql task?

Create a procedure with script inside.
make filename as a parameter in it
From For Each Loop you can retrieve filename and store it in a SSIS variable created.
then use the variable to pass value for the parameter to procedure

The statement would be like

EXEC ProcedureName ?

and map the placeholder to variable you created in Parameter mapping tab and code gets executed for each file it gets from the folder

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources