我正在尝试将一些XML导入SQL,但是它没有起作用。这是我使用的SQL:
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);
这里是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>
查询没有错误,只是没有插入任何行。有人能解释我做错了什么吗?