Import XML to SQL

Question

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://winscp.net/schema/session/1.0" 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!

Answer 1

You just simply need to respect the XML namespace that's defined on your XML root node!

<session xmlns="http://winscp.net/schema/session/1.0" 
         ********************************************

To accommodate this XML namespace, you need to define in in your XQuery - best approach in my opinion is by using the WITH XMLNAMESPACES statement:

;WITH XMLNAMESPACES(DEFAULT 'http://winscp.net/schema/session/1.0')
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);