sql-serverxmlxml-import

Import Multi-level (element) XML file into MS SQL Server Table


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

Solution

  • 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