XML To Sql Server

Question

I am trying to import an xml file into the sql server with no success yet.
The xml file is structured like this:

<Users xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  
  <User>
    <PartitionKey>be-BY</PartitionKey>
  </User>
</Users>

I am using the following code:

   SELECT 
    xmldata.value('(PartitionKey)[1]', 'NCHAR(10)') AS 'partition_key'  
FROM 
    (SELECT CAST(x AS XML)
     FROM OPENROWSET(
        BULK 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\SkillageXML\userstest1111.xml',
        SINGLE_BLOB
        ) 
        AS T(x)
    )  AS T(x)
CROSS APPLY 
    x.nodes('/Users/User') AS X(xmldata);

However, I don't see any value after it is done with processing the file. Is there anything missing?

marc_s · Accepted Answer · 2013-07-18 14:09:47Z

6

This works like a charm for me:

SELECT 
    XUsers.value('(PartitionKey)[1]', 'NCHAR(10)') AS 'partition_key'  
FROM 
    (SELECT 
        BulkXML = CAST(BulkColumn AS XML)
     FROM 
        OPENROWSET(BULK 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\SkillageXML\userstest1111.xml', SINGLE_BLOB) AS BX 
    ) AS T
CROSS APPLY 
    BulkXml.nodes('/Users/User') AS XTbl(XUsers);and returns:

partition_key
be-BY

I think you're approach of using the AS T(x) alias twice is causing confusion - try to use something more meaningful and not the same alias for both things.

edited Jul 18, 2013 at 14:09

answered Jul 18, 2013 at 13:07

marc_s

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

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

1 Comment

OzrenTkalcecKrznaric Over a year ago

Use INSERT INTO ... SELECT FROM

Collectives™ on Stack Overflow

XML To Sql Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related