Import XML to SQL



Sorry to put one more post regarding this topic, but I am desperate trying to import this xml to the database without any success. This is the xml:



<session xmlns="http://ift.tt/1q9roaP" start="2014-11-03T17:23:22.376Z">
<ls>
<destination value="/Output" />
<files>
<file>
<filename value="." />
<type value="d" />
<modification value="2014-11-03T12:17:58.000Z" />
<permissions value="rwxr-sr-x" />
</file>
<file>
<filename value="7215_SG.csv" />
<type value="-" />
<size value="1584161" />
<modification value="2014-11-03T12:06:46.000Z" />
<permissions value="rw-r--r--" />
</file>
<file>
<filename value="6171_SG.csv" />
<type value="-" />
<size value="2298481" />
<modification value="2014-11-03T12:05:13.000Z" />
<permissions value="rw-r--r--" />
</file>
</files>
<result success="true" />
</ls>
</session>


And this is the sql statement to import the xml:



SELECT
xmldata.value('(./filename/@value)[1]', 'NVARCHAR(50)') AS szFilename,
xmldata.value('(./type/@value)[1]', 'NVARCHAR(50)') AS szType,
xmldata.value('(./size/@value)[1]', 'NVARCHAR(50)') AS szSize,
xmldata.value('(./modification/@value)[1]', 'NVARCHAR(50)') AS szModification,
xmldata.value('(./permissions/@value)[1]', 'NVARCHAR(50)') AS szPermissions
FROM
(SELECT CAST(x AS XML)
FROM OPENROWSET(BULK 'd:\temp\Test\log.xml',
SINGLE_BLOB) AS T(x)) AS T(x)
CROSS APPLY
x.nodes('//session/ls/files/file') AS X(xmldata);

Can you help me and tell me what am I doing wrong?


Thanks in advance!


No comments:

Post a Comment