将多级(元素)XML 文件导入 MS SQL Server 表中

Question

我正在尝试将多级/元素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:UsersalindDownloads2018test1, 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

Answer 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