Importing XML into SQL

Question

I'm trying to import some XML into SQL but its not working. This is the SQL I'm using:

INSERT INTO learnertest (LearnRefNumber, FamilyName,GivenNames ) 
SELECT learner.query('LearnRefNumber').value('.', 'VARCHAR(15)'),
       learner.query('FamilyName').value('.', 'VARCHAR(30)'), 
       learner.query('GivenNames').value('.', 'VARCHAR(30)')
FROM ( 
SELECT CAST(x AS XML)
FROM OPENROWSET(
     BULK 'D:\xmltest.xml',
     SINGLE_BLOB) AS T(x)
     ) AS T(x)
CROSS APPLY x.nodes('Message/Learner') AS X(learner);

and here is the XML

<?xml version="1.0" encoding="utf-8" ?>
<Message xsi:schemaLocation="http://www.theia.org.uk/ILR/2011-12/1 ILR-2012-13-Structure.xsd" xmlns="http://www.theia.org.uk/ILR/2012-13/1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <Header>
        <CollectionDetails>
            <Collection>ILR</Collection>
            <Year>1213</Year>
            <FilePreparationDate>2013-09-30</FilePreparationDate>
        </CollectionDetails>
        <Source>
            <ProtectiveMarking>PROTECT-PRIVATE</ProtectiveMarking>
            <UKPRN>10000000</UKPRN>
            <TransmissionType>A</TransmissionType>
            <SoftwareSupplier>xxx Ltd</SoftwareSupplier>
            <SoftwarePackage>xx</SoftwarePackage>
            <Release>7.12</Release>
            <SerialNo>1</SerialNo>
            <DateTime>2013-09-30T09:41:24</DateTime>
        </Source>
    </Header>
    <LearningProvider>
        <UKPRN>10004840</UKPRN>
    </LearningProvider>
    <Learner>
        <LearnRefNumber>3333333</LearnRefNumber>
        <ULN>44444444</ULN>
        <FamilyName>Jones</FamilyName>
        <GivenNames>Jane</GivenNames>
        <DateOfBirth>1993-11-12</DateOfBirth>
        <Ethnicity>31</Ethnicity>
        <Sex>F</Sex>
        <LLDDHealthProb>1</LLDDHealthProb>
        <Domicile>XF</Domicile>
        <PriorAttain>1</PriorAttain>
        <ALSCost>0</ALSCost>
        <Dest>97</Dest>
    </Learner>
</Message>

the query runs without error, it just doesnt insert any rows. Can anyone explain what I'm doing wrong?

Slightly confused by that, I'm quite new to XML. Are you suggesting I should edit the XML its self? — Marshall10001, Oct 02 '13 at 10:48

score 1 · Accepted Answer · edited May 23 '17 at 12:03

Because your Message element is in a namespace, either your xpaths also need to be in that namespace, or you need to tell SQL to assume a default namespace. The latter is the easier option in this simple case:

;WITH XMLNAMESPACES(DEFAULT 'http://www.theia.org.uk/ILR/2012-13/1')
INSERT INTO learnertest (LearnRefNumber, FamilyName,GivenNames ) 
SELECT learner.query('LearnRefNumber').value('.', 'VARCHAR(15)'),
       learner.query('FamilyName').value('.', 'VARCHAR(30)'), 
        learner.query('GivenNames').value('.', 'VARCHAR(30)')
FROM ( 
SELECT CAST(x AS XML)
FROM OPENROWSET(
     BULK 'D:\xmltest.xml',
     SINGLE_BLOB) AS T(x)
     ) AS T(x)
CROSS APPLY x.nodes('Message/Learner') AS X(learner);

Simply adding that first line should do the trick.

Importing XML into SQL

1 Answers1