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!

Assuming this is for sql-server - just SQL only denotes the query language - but not the database..... please always provide the concrete database (and preferably also the version of it) for your database-related questions! — marc_s
– marc_s, Commented Nov 4, 2014 at 12:54
What is the error you are getting? (if any) This information is always helpful to include. — asontu
– asontu, Commented Nov 4, 2014 at 12:56
I dont have any error. The problem is that the query does not return any values. — user2811907
– user2811907, Commented Nov 4, 2014 at 14:20

marc_s · Accepted Answer · 2014-11-04 12:49:19Z

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

Thanks marc_s but what if the server does not have internet access to get the namespace. Is there away to get the data without the use of the namespace?
@user2811907: the XML namespace is just a string! it has nothing to do with the internet - you don't need any internet connection to run this

Collectives™ on Stack Overflow

Import XML to SQL

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related