Reputation: 413
I've got a problem where I'm importing the contents of an XML file into MSSQL. However I am only getting data in the first column.
A sample of what my XML looks like:
<ArrayOfPseudoAnimal xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<PseudoAnimal>
<ID>a77b121e-08ec-4f1a-a997-387a8e0652d1</ID>
<AnimalID>UK168277300003</AnimalID>
<HoldingID>1e988796-2987-4d3f-964b-f12ae582d1bf</HoldingID>
<DateIn>2015-06-24T00:00:00</DateIn>
<DOB>2010-12-17T00:00:00</DOB>
<Sex>Female</Sex>
<BreedCode>HO</BreedCode>
<ImportDate>2013-06-28T00:00:00</ImportDate>
<DataDate xsi:nil="true" />
<MilkRecordManagementID>3</MilkRecordManagementID>
<MilkRecordOfficialID>168277300003</MilkRecordOfficialID>
<MilkRecordUniqueID>4614659</MilkRecordUniqueID>
<ManagementID>3</ManagementID>
<NoOfCalves>3</NoOfCalves>
</PseudoAnimal>
<PseudoAnimal>
<ID>2eff9a24-f0e5-4a4d-ad4d-e2f15dc6565a</ID>
<AnimalID>UK168277700007</AnimalID>
<HoldingID>1e988796-2987-4d3f-964b-f12ae582d1bf</HoldingID>
<DateIn>2010-12-22T00:00:00</DateIn>
<DOB>2010-12-22T00:00:00</DOB>
<Sex>Female</Sex>
<BreedCode>HO</BreedCode>
<ImportDate>2013-06-28T00:00:00</ImportDate>
<DataDate xsi:nil="true" />
<MilkRecordManagementID>7</MilkRecordManagementID>
<MilkRecordOfficialID>168277700007</MilkRecordOfficialID>
<MilkRecordUniqueID>4614681</MilkRecordUniqueID>
<ManagementID>7</ManagementID>
<NoOfCalves>4</NoOfCalves>
</PseudoAnimal>
</ArrayOfPseudoAnimal>
The following is the SQL I am using
DECLARE @XMLData XML
SELECT @XMLData = CONVERT(XML, BulkColumn)
FROM OPENROWSET(BULK 'G:\Animals.xml', SINGLE_BLOB) AS x
DECLARE @hDoc AS INT, @SQL NVARCHAR(MAX)
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XMLData
SELECT *
FROM OPENXML(@hDoc, 'ArrayOfPseudoAnimal/PseudoAnimal', 2)
WITH (
ID UNIQUEIDENTIFIER
, animalID NVARCHAR(50)
, holdingID UNIQUEIDENTIFIER
, dateIn DATETIME
, dob DATETIME
, methodOfDisposal NVARCHAR(255)
, sex NVARCHAR(10)
, breedCode NVARCHAR(50)
, importDate DATETIME
, dataDate DATETIME
, milkRecordManagementID NVARCHAR(50)
, milkRecordOfficialID NVARCHAR(50)
, milkRecordUniqueID NVARCHAR(50)
, managementID NVARCHAR(50)
, managementGroup UNIQUEIDENTIFIER
, noOfCalves INT
)
However when I run this the only column in the results pane is the ID column, with Management Studio telling me that the Query executed successfully.
Any suggestions as to what I'm doing wrong?
Upvotes: 0
Views: 125