I'm attempting to import a multi-level/element XML file into an MS SQL Server (Express 2017) table. I'm successfully importing the first element details (TVLTagDetails) into the table, but not the following two element details (TVLAccountDetails and TVLPlateDetails). Although I've worked with SQL queries before, it's been years ago and I'm not a developer. I would really appreciate some help with the import syntax. Please see the following code and example XML file.
Here are the queries I'm currently using:
CREATE TABLE [TVLTagDetails06](
[ID] [int] IDENTITY(1,1) NOT NULL,
[HomeAgencyID] [varchar](4) NOT NULL,
[TagAgencyID] [varchar](4) NOT NULL,
[TagSerialNumber] [varchar](8) NOT NULL,
[TagStatus] [varchar](4) NOT NULL,
[TagClass] [varchar](4) NOT NULL,
[PlateCountry] [varchar](4) NOT NULL,
[PlateState] [varchar](4) NOT NULL,
[PlateNumber] [varchar](12) NOT NULL,
[AccountNumber] [varchar](12) NOT NULL,
CONSTRAINT [PK] PRIMARY KEY ([Id])
)
GO
INSERT INTO dbo.TVLTagDetails06 (HomeAgencyID, TagAgencyID, TagSerialNumber, TagStatus, TagClass, PlateCountry, PlateState, PlateNumber, AccountNumber)
SELECT
MY_XML.Details.query('HomeAgencyID').value('.', 'VARCHAR(4)'),
MY_XML.Details.query('TagAgencyID').value('.', 'VARCHAR(4)'),
MY_XML.Details.query('TagSerialNumber').value('.', 'VARCHAR(8)'),
MY_XML.Details.query('TagStatus').value('.', 'VARCHAR(4)'),
MY_XML.Details.query('TagClass').value('.', 'VARCHAR(4)'),
MY_XML.Details.query('PlateCountry').value('.', 'VARCHAR(4)'),
MY_XML.Details.query('PlateState').value('.', 'VARCHAR(4)'),
MY_XML.Details.query('PlateNumber').value('.', 'VARCHAR(4)'),
MY_XML.Details.query('AccountNumber').value('.', 'VARCHAR(4)')
FROM (SELECT CAST(MY_XML AS xml)
FROM OPENROWSET(BULK 'C:\Users\alind\Downloads\2018\test1, all 3 types.xml', SINGLE_BLOB) AS T(MY_XML)) AS T(MY_XML)
CROSS APPLY MY_XML.nodes('TagValidationList/TVLDetail/TVLTagDetails, TagValidationList/TVLDetail/TVLPlateDetails, TagValidationList/TVLDetail/TVLAccountDetails') AS MY_XML (Details);
Select * from dbo.TVLTagDetails06
GO
Here is a sample XML file that contains the variation of detail I may encounter:
<?xml version="1.0" encoding="UTF-8"?>
<TagValidationList>
<TVLHeader>
<SubmissionType>STVL</SubmissionType>
<SubmissionDateTime>2000-01-00T00:00:01Z</SubmissionDateTime>
<SSIOPHubID>0001</SSIOPHubID>
<HomeAgencyID>1002</HomeAgencyID>
<BulkIndicator>B</BulkIndicator>
<BulkIdentifier>100</BulkIdentifier>
<RecordCount>3</RecordCount>
</TVLHeader>
<TVLDetail>
<TVLTagDetails>
<HomeAgencyID>1234</HomeAgencyID>
<TagAgencyID>1100</TagAgencyID>
<TagSerialNumber>00123456</TagSerialNumber>
<TagStatus>X</TagStatus>
<TagClass>1</TagClass>
<TVLAccountDetails/>
</TVLTagDetails>
<TVLTagDetails>
<HomeAgencyID>2234</HomeAgencyID>
<TagAgencyID>1200</TagAgencyID>
<TagSerialNumber>00223456</TagSerialNumber>
<TagStatus>Y</TagStatus>
<TagClass>2</TagClass>
<TVLPlateDetails>
<PlateCountry>US</PlateCountry>
<PlateState>TX</PlateState>
<PlateNumber>123ABC</PlateNumber>
<PlateEffectiveFrom>2008-03-12T06:00:00Z</PlateEffectiveFrom>
</TVLPlateDetails>
<TVLAccountDetails/>
</TVLTagDetails>
<TVLTagDetails>
<HomeAgencyID>3234</HomeAgencyID>
<TagAgencyID>1300</TagAgencyID>
<TagSerialNumber>12345678</TagSerialNumber>
<TagStatus>Z</TagStatus>
<TagClass>3</TagClass>
<TVLPlateDetails>
<PlateCountry>US</PlateCountry>
<PlateState>OK</PlateState>
<PlateNumber>ABC321</PlateNumber>
</TVLPlateDetails>
<TVLAccountDetails>
<AccountNumber>654321</AccountNumber>
</TVLAccountDetails>
</TVLTagDetails>
</TVLDetail>
</TagValidationList>
What I need is to get all the detail elements into one row. For example:
ID HomeAgencyID TagAgencyID TagSerialNumber TagStatus TagClass PlateCountry PlateState PlateNumber AccountNumber
1 1234 1100 00123456 X 1 US
2 2234 1200 00223456 Y 2 US TX 123ABC
3 3234 1300 12345678 Z 3 US OK ABC321 654321
You can build an insert statement around the SELECT below. There may be a better way to do this, but this can at least move you forward a bit.
declare @x as xml;
set @x = '<?xml version="1.0" encoding="UTF-8"?>
<TagValidationList>
<TVLHeader>
<SubmissionType>STVL</SubmissionType>
<SubmissionDateTime>2000-01-00T00:00:01Z</SubmissionDateTime>
<SSIOPHubID>0001</SSIOPHubID>
<HomeAgencyID>1002</HomeAgencyID>
<BulkIndicator>B</BulkIndicator>
<BulkIdentifier>100</BulkIdentifier>
<RecordCount>3</RecordCount>
</TVLHeader>
<TVLDetail>
<TVLTagDetails>
<HomeAgencyID>1234</HomeAgencyID>
<TagAgencyID>1100</TagAgencyID>
<TagSerialNumber>00123456</TagSerialNumber>
<TagStatus>X</TagStatus>
<TagClass>1</TagClass>
<TVLAccountDetails/>
</TVLTagDetails>
<TVLTagDetails>
<HomeAgencyID>2234</HomeAgencyID>
<TagAgencyID>1200</TagAgencyID>
<TagSerialNumber>00223456</TagSerialNumber>
<TagStatus>Y</TagStatus>
<TagClass>2</TagClass>
<TVLPlateDetails>
<PlateCountry>US</PlateCountry>
<PlateState>TX</PlateState>
<PlateNumber>123ABC</PlateNumber>
<PlateEffectiveFrom>2008-03-12T06:00:00Z</PlateEffectiveFrom>
</TVLPlateDetails>
<TVLAccountDetails/>
</TVLTagDetails>
<TVLTagDetails>
<HomeAgencyID>3234</HomeAgencyID>
<TagAgencyID>1300</TagAgencyID>
<TagSerialNumber>12345678</TagSerialNumber>
<TagStatus>Z</TagStatus>
<TagClass>3</TagClass>
<TVLPlateDetails>
<PlateCountry>US</PlateCountry>
<PlateState>OK</PlateState>
<PlateNumber>ABC321</PlateNumber>
</TVLPlateDetails>
<TVLAccountDetails>
<AccountNumber>654321</AccountNumber>
</TVLAccountDetails>
</TVLTagDetails>
</TVLDetail>
</TagValidationList>';
SELECT
t.x.value('HomeAgencyID[1]', 'varchar(4)') as HomeAgencyID,
t.x.value('TagAgencyID[1]', 'varchar(4)') as TagAgencyID,
t.x.value('TagSerialNumber[1]', 'varchar(8)') as TagSerialNumber,
t.x.value('TagStatus[1]', 'varchar(4)') as TagStatus,
t.x.value('TagClass[1]', 'varchar(4)') as TagClass,
t.x.value('(TVLPlateDetails/PlateCountry)[1]', 'varchar(4)') as PlateCountry,
t.x.value('(TVLPlateDetails/PlateState)[1]', 'varchar(4)') as PlateState,
t.x.value('(TVLPlateDetails/PlateNumber)[1]', 'varchar(12)') as PlateNumber,
t.x.value('(TVLAccountDetails/AccountNumber)[1]', 'varchar(12)') as AccountNumber
FROM @x.nodes('/TagValidationList/TVLDetail/TVLTagDetails') t(x)
The output is close to what you're looking for. You can ISNULL-'' some of the outputs if you need to.
HomeAgencyID TagAgencyID TagSerialNumber TagStatus TagClass PlateCountry PlateState PlateNumber AccountNumber
------------ ----------- --------------- --------- -------- ------------ ---------- ------------ -------------
1234 1100 00123456 X 1 NULL NULL NULL NULL
2234 1200 00223456 Y 2 US TX 123ABC NULL
3234 1300 12345678 Z 3 US OK ABC321 654321