Импорт многоуровневого (элементного) XML-файла в таблицу MS SQL Server
Я пытаюсь импортировать многоуровневый / элементный XML-файл в таблицу MS SQL Server (Express 2017). Я успешно импортирую первые детали элемента (TVLTagDetails) в таблицу, но не следующие две детали элемента (TVLAccountDetails и TVLPlateDetails). Хотя я работал с SQL-запросами раньше, это было много лет назад, и я не разработчик. Я был бы очень признателен за помощь с синтаксисом импорта. Пожалуйста, смотрите следующий код и пример XML-файла.
Вот запросы, которые я сейчас использую:
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
Вот пример XML-файла, который содержит вариацию деталей, с которыми я могу столкнуться:
<?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>
Что мне нужно, это собрать все элементы детализации в один ряд. Например:
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
1 ответ
Решение
Вы можете построить оператор вставки вокруг SELECT ниже. Возможно, есть лучший способ сделать это, но это может, по крайней мере, немного продвинуть вас вперед.
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)
Результат близок к тому, что вы ищете. Вы можете ISNULL-'' некоторые из выходов, если вам нужно.
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