Richard Boyce
Richard Boyce

Reputation: 413

Using OPENXML in MSSQL I'm only getting values from the first column

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

Answers (0)

Related Questions