Import structured XML file to SQL table

Question

I'm trying to download and import a XML file to an SQL Table. The download is working perfectly and it generates the following XML file.

GeoPers.xml

<?xml version="1.0" encoding="UTF-8"?>
<soap:Envelope xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
    <Clocking_GetByDateRangeUtcResponse xmlns="http://www.geodynamics.be/webservices">
        <Clocking_GetByDateRangeUtcResult>
            <ClockingEntity>
                <Id>af261a77-a35b-4778-a175-6fd50d00cde1</Id>
                <DateTimeUtc>2017-09-28T02:37:56Z</DateTimeUtc>
                <DateTimeLocal>2017-09-28T04:37:56</DateTimeLocal>
                <User>
                    <Id>4975535a-2690-4492-bc7f-5e8a7d839cb9</Id>
                    <Name>Marcel De Doncker</Name>
                    <DayProgramId xsi:nil="true"/>
                </User>
                <Vehicle>
                    <Id>52e01d9c-96fa-46ed-98fe-f62e1792f019</Id>
                    <Code>7080</Code>
                    <Name>Auto: Dacia Dokker 1-PAE-120</Name>
                    <VehicleTypeId xsi:nil="true"/>
                    <LastSyncDateUtc xsi:nil="true"/>
                </Vehicle>
                <Type>StartMovementDriver</Type>
                <Location>
                <Address>
                    <Street>Kapelleveld</Street>
                    <HouseNumber>49</HouseNumber>
                    <PostalCode>1785</PostalCode>
                    <City>Merchtem</City>
                    <Submunicipality>Merchtem</Submunicipality>
                    <Country>Belgium</Country>
                </Address>
                <Longitude>4.2380828857421875</Longitude>
                <Latitude>50.9540901184082</Latitude>
                </Location>
                <Pois/>
                <IsManual>false</IsManual>
            </ClockingEntity>
        </Clocking_GetByDateRangeUtcResult>
    </Clocking_GetByDateRangeUtcResponse>
</soap:Body>
</soap:Envelope>

I'm using the following query to load the file into SQL:

SQL QUERY

DECLARE @XmlFile XML

SELECT @XmlFile = BulkColumn  
FROM OPENROWSET(BULK 'C:\Temp\GeoDynamics\Downloads\GeoPers.xml', SINGLE_BLOB) x

SELECT 
    Id = resource.value('(Id)[1]', 'varchar(255)'),
    DateTimeUtc = resource.value('(DateTimeUtc)[2]', 'varchar(255)'),
    DateTimeLocal = resource.value('(DateTimeLocal)[3]', 'varchar(255)'),
    UserID = resource.value('(User/Id)[4]', 'varchar(255)'),
    UserName = resource.value('(User/Name)[5]', 'varchar(255)'),
    VehicleID = resource.value('(Vehicle/Id)[6]', 'varchar(255)'),
    VehicleCode = resource.value('(Vehicle/Code)[7]', 'varchar(255)'),
    VehicleName = resource.value('(Vehicle/Name)[8]', 'varchar(255)')
 FROM
@XmlFile.nodes('//Clocking_GetByDateRangeUtcResponse/Clocking_GetByDateRangeUtcResult/ClockingEntity') AS XTbl1(resource)

The query gives no result, not even an error code. I don't know where I'm wrong in the query.

Richard Deeming · Accepted Answer · 2017-09-28 11:27:56Z

The main problem is that your XML has a default namespace (http://www.geodynamics.be/webservices). You will need to use the WITH XMLNAMESPACES directive to specify that namespace for your query.

As mentioned in the other answer, you will also need to fix the indexes in your XPath queries - they should all be [1].

WITH XMLNAMESPACES (DEFAULT 'http://www.geodynamics.be/webservices')
SELECT 
    Id = resource.value('(Id)[1]', 'varchar(255)'),
    DateTimeUtc = resource.value('(DateTimeUtc)[1]', 'varchar(255)'),
    DateTimeLocal = resource.value('(DateTimeLocal)[1]', 'varchar(255)'),
    UserID = resource.value('(User/Id)[1]', 'varchar(255)'),
    UserName = resource.value('(User/Name)[1]', 'varchar(255)'),
    VehicleID = resource.value('(Vehicle/Id)[1]', 'varchar(255)'),
    VehicleCode = resource.value('(Vehicle/Code)[1]', 'varchar(255)'),
    VehicleName = resource.value('(Vehicle/Name)[1]', 'varchar(255)')
FROM
    @XmlFile.nodes('//Clocking_GetByDateRangeUtcResponse/Clocking_GetByDateRangeUtcResult/ClockingEntity') AS XTbl1(resource);

Output:

Id                af261a77-a35b-4778-a175-6fd50d00cde1
DateTimeUtc       2017-09-28T02:37:56Z
DateTimeLocal     2017-09-28T04:37:56
UserID            4975535a-2690-4492-bc7f-5e8a7d839cb9
UserName          Marcel De Doncker
VehicleID         52e01d9c-96fa-46ed-98fe-f62e1792f019
VehicleCode       7080
VehicleName       Auto: Dacia Dokker 1-PAE-120

Add Namespaces to Queries with WITH XMLNAMESPACES

TT. · Accepted Answer · 2017-09-28 11:28:07Z

This will work. You are not selecting in the correct namespace; use WITH XMLNAMESPACES for this. Second, you are selecting with the wrong index apart from the Id element; the index [n] you specify will select the n-th occurance of the element. You always need to first one.

DECLARE @x XML=N'
<soap:Envelope xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
    <Clocking_GetByDateRangeUtcResponse xmlns="http://www.geodynamics.be/webservices">
        <Clocking_GetByDateRangeUtcResult>
            <ClockingEntity>
                <Id>af261a77-a35b-4778-a175-6fd50d00cde1</Id>
                <DateTimeUtc>2017-09-28T02:37:56Z</DateTimeUtc>
                <DateTimeLocal>2017-09-28T04:37:56</DateTimeLocal>
                <User>
                    <Id>4975535a-2690-4492-bc7f-5e8a7d839cb9</Id>
                    <Name>Marcel De Doncker</Name>
                    <DayProgramId xsi:nil="true"/>
                </User>
                <Vehicle>
                    <Id>52e01d9c-96fa-46ed-98fe-f62e1792f019</Id>
                    <Code>7080</Code>
                    <Name>Auto: Dacia Dokker 1-PAE-120</Name>
                    <VehicleTypeId xsi:nil="true"/>
                    <LastSyncDateUtc xsi:nil="true"/>
                </Vehicle>
                <Type>StartMovementDriver</Type>
                <Location>
                <Address>
                    <Street>Kapelleveld</Street>
                    <HouseNumber>49</HouseNumber>
                    <PostalCode>1785</PostalCode>
                    <City>Merchtem</City>
                    <Submunicipality>Merchtem</Submunicipality>
                    <Country>Belgium</Country>
                </Address>
                <Longitude>4.2380828857421875</Longitude>
                <Latitude>50.9540901184082</Latitude>
                </Location>
                <Pois/>
                <IsManual>false</IsManual>
            </ClockingEntity>
        </Clocking_GetByDateRangeUtcResult>
    </Clocking_GetByDateRangeUtcResponse>
</soap:Body>
</soap:Envelope>';

WITH XMLNAMESPACES(DEFAULT 'http://www.geodynamics.be/webservices' )
SELECT 
    Id = resource.value('(Id)[1]', 'varchar(255)'),
    DateTimeUtc = resource.value('(DateTimeUtc)[1]', 'varchar(255)'),
    DateTimeLocal = resource.value('(DateTimeLocal)[1]', 'varchar(255)'),
    UserID = resource.value('(User/Id)[1]', 'varchar(255)'),
    UserName = resource.value('(User/Name)[1]', 'varchar(255)'),
    VehicleID = resource.value('(Vehicle/Id)[1]', 'varchar(255)'),
    VehicleCode = resource.value('(Vehicle/Code)[1]', 'varchar(255)'),
    VehicleName = resource.value('(Vehicle/Name)[1]', 'varchar(255)')
 FROM
    @x.nodes('//Clocking_GetByDateRangeUtcResponse/Clocking_GetByDateRangeUtcResult/ClockingEntity') AS XTbl1(resource)

Collectives™ on Stack Overflow

Import structured XML file to SQL table

GeoPers.xml

SQL QUERY

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

GeoPers.xml

SQL QUERY

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related