XML import into SQL Server

Question

这是我第一次使用XML文件。我已经能够将文件读入表中。现在我正在尝试访问数据元素以插入/更新到ERP中。

我被困在以 https://learn.microsoft.com/en-us/sql/t-sql/functions/openxml-transact-sql 为指导的地方。

我现在只尝试一次检索，试图让事情变得简单： .XML：

<TranscriptRequest xmlns="urn:org:pesc:message:TranscriptRequest:v1.0.0">
<TransmissionData>
<DocumentID xmlns="">88895-20170227180832302-ccd</DocumentID>
<CreatedDateTime xmlns="">2017-02-27T18:08:32.303-08:00</CreatedDateTime>
<DocumentTypeCode xmlns="">Request</DocumentTypeCode>
<TransmissionType xmlns="">Original</TransmissionType>
<Source xmlns="">
<Organization>
<DUNS>626927060</DUNS>
<OrganizationName>AVOW</OrganizationName>
</Organization>
</Source>
<Destination xmlns="">
<Organization>
<OPEID>3419</OPEID>
<OrganizationName>Charleston Southern University</OrganizationName>
</Organization>
</Destination>
<DocumentProcessCode xmlns="">PRODUCTION</DocumentProcessCode>
</TransmissionData>
<Request>
<CreatedDateTime xmlns="">2017-02-27T00:00:00.000-08:00</CreatedDateTime>
<Requestor xmlns="">
<Person>
<Birth>
<BirthDate>1985-01-01</BirthDate>
</Birth>
<Name>
<FirstName>Chad</FirstName>
<LastName>test2</LastName>
</Name>
<AlternateName>
<FirstName>Chad</FirstName>
<LastName>Walker</LastName>
<CompositeName>Walker, Chad</CompositeName>
</AlternateName>
<Contacts>
<Address>
<AddressLine>10260 west st</AddressLine>
<City>Denver</City>
<StateProvinceCode>CO</StateProvinceCode>
<PostalCode>80236</PostalCode>
</Address>
<Phone>
<CountryPrefixCode>1</CountryPrefixCode>
<AreaCityCode>303</AreaCityCode>
<PhoneNumber>8152848</PhoneNumber>
</Phone>
<Email>
<EmailAddress>[e-mail]</EmailAddress>
</Email>
</Contacts>
</Person>
</Requestor>

我的 SQL：

USE TMSEPRD
DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = [XMLData] FROM [dbo].[CSU_Parchment_XMLwithOpenXML]
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
SELECT BirthDate
FROM OPENXML(@hDoc, 'ROOT/Request/Requestor/Person/Birth/Birthdate')
WITH 
(
BirthDate varchar(20) '../@BirthDate'
)
EXEC sp_xml_removedocument @hDoc
GO

Birthdate保持空白。到目前为止，我已经尝试消除../在@Birthdate面前，我尝试删除@，我尝试从路径中删除Birthdate。

Answer 1

使用 xml 数据类型方法

DECLARE @XML XML='<TranscriptRequest xmlns="urn:org:pesc:message:TranscriptRequest:v1.0.0">
<TransmissionData>
<DocumentID xmlns="">88895-20170227180832302-ccd</DocumentID>
<CreatedDateTime xmlns="">2017-02-27T18:08:32.303-08:00</CreatedDateTime>
<DocumentTypeCode xmlns="">Request</DocumentTypeCode>
<TransmissionType xmlns="">Original</TransmissionType>
<Source xmlns="">
<Organization>
<DUNS>626927060</DUNS>
<OrganizationName>AVOW</OrganizationName>
</Organization>
</Source>
<Destination xmlns="">
<Organization>
<OPEID>3419</OPEID>
<OrganizationName>Charleston Southern University</OrganizationName>
</Organization>
</Destination>
<DocumentProcessCode xmlns="">PRODUCTION</DocumentProcessCode>
</TransmissionData>
<Request>
<CreatedDateTime xmlns="">2017-02-27T00:00:00.000-08:00</CreatedDateTime>
<Requestor xmlns="">
<Person>
<Birth>
<BirthDate>1985-01-01</BirthDate>
</Birth>
<Name>
<FirstName>Chad</FirstName>
<LastName>test2</LastName>
</Name>
<AlternateName>
<FirstName>Chad</FirstName>
<LastName>Walker</LastName>
<CompositeName>Walker, Chad</CompositeName>
</AlternateName>
<Contacts>
<Address>
<AddressLine>10260 west st</AddressLine>
<City>Denver</City>
<StateProvinceCode>CO</StateProvinceCode>
<PostalCode>80236</PostalCode>
</Address>
<Phone>
<CountryPrefixCode>1</CountryPrefixCode>
<AreaCityCode>303</AreaCityCode>
<PhoneNumber>8152848</PhoneNumber>
</Phone>
<Email>
<EmailAddress>[e-mail]</EmailAddress>
</Email>
</Contacts>
</Person>
</Requestor>
</Request>
</TranscriptRequest>';  

SQL查询：

with xmlnamespaces('urn:org:pesc:message:TranscriptRequest:v1.0.0' as ns)
select t.n.value('BirthDate[1]','date')
from @XML.nodes('ns:TranscriptRequest/ns:Request/Requestor/Person/Birth') t(n);

Answer 2

命名空间

此 XML 是如何生成的？这在你的控制之下吗？让我想知道的是各种xmlns=""这是一遍又一遍地定义空的默认命名空间......尽管第一行中有一个默认的 NSxmlns="urn:org:pesc:message:TranscriptRequest:v1.0.0"，但这似乎是正确的。

对于此命名空间问题，有许多解决方案：

• 在没有空xmlns的情况下创建它 - 如果这在您的控制之下
• 在将REPLACE写入 xml 类型列/变量之前，在字符串级别上切掉xmlns=""(注意前导空格！
• 对前两个级别的命名空间使用通配符 (*:)
• 为默认值定义命名空间别名，并让空命名空间成为较低级别的默认值

结构

您的XML似乎是普通1:1，只有1个源，1个目的地，一个要求。而且<Request>内只有 1 个人，下面只有 1 个名字等等<Person>.有一个<Contacts>元素，听起来像1:n，但它似乎只包括 1 个地址、1 个电话......阅读简单的1:1很容易...

我将在.query()中使用带有两个调用的 CTE，以使用通配符解决命名空间问题的简单方法。这允许在没有命名空间的情况下读取其余部分：

一些示例如何读取您的元素，其余的取决于您...

WITH levels AS
(
SELECT @xml.query(N'/*:TranscriptRequest/*:TransmissionData/*') AS td
,@xml.query(N'/*:TranscriptRequest/*:Request/*') AS rq
)
SELECT --TransmissionData
td.value(N'(DocumentID/text())[1]',N'nvarchar(max)') AS DocumentID
,td.value(N'(CreatedDateTime/text())[1]',N'datetime') AS td_CreatedDateTime
--more elemenst...
,td.value(N'(Source/Organization/DUNS/text())[1]',N'bigint') AS Source_Organization_DUNS
--more elements...
,td.value(N'(Destination/Organization/OPEID/text())[1]',N'bigint') AS Destination_Organization_OPEID
--Request
,rq.value(N'(CreatedDateTime/text())[1]',N'datetime') AS rq_CreatedDateTime
--Request-Person
,rq.value(N'(Requestor/Person/Name/FirstName/text())[1]',N'nvarchar(max)') AS Requestor_FirstName
--Contacts 
,rq.value(N'(Requestor/Person/Contacts/Address/AddressLine/text())[1]',N'nvarchar(max)') AS Requestor_AddressLine

FROM levels