我有這個 XML，我試圖從中檢索一個值...
<ExportPartyAddressingResponse xmlns="http://www.ediel.no/Export">
<CompanyListMessage>
<Header GeneratedAt="2021-11-11T05:00:02Z" SenderApplication="www.ediel.se" />
<Market Code="EL" CountryCode="SE">
<Company>
<Name>7H Kraft Energif??rmedling AB</Name>
<ContactInformation>
<Item Type="Telephone"> 46321532323</Item>
<Item Type="Telefax"> 46321532321</Item>
<Item Type="Web">www.7hkraft.se</Item>
</ContactInformation>
<Identifiers>
<Key Type="EdielId">60900</Key>
<Key Type="OrgNo">556525-8075</Key>
<Key Type="SvKId">7HK</Key>
</Identifiers>
<Addresses>
<Address Type="Postal">
<Address1>Box 25</Address1>
<PostCode>22100</PostCode>
<Place>LUND</Place>
<CountryCode>SE</CountryCode>
</Address>
</Addresses>
<Roles>
<Role>PowerSupplier</Role>
</Roles>
<EdielAddressing>
<EDIFACTAddressing>
<EDIFACTDetails Type="PRODAT">
<EDICharset>UNOC</EDICharset>
<EDISyntax>3</EDISyntax>
<CommunicationAddress Type="SMTP">[email protected]</CommunicationAddress>
<InterchangePartyId IdCodeQualifier="ZZ">60900</InterchangePartyId>
<PartyId IdCodeQualifier="160" IdCodeResponsible="SVK">60900</PartyId>
</EDIFACTDetails>
<EDIFACTDetails Type="UTILTS">
<EDICharset>UNOC</EDICharset>
<EDISyntax>3</EDISyntax>
<CommunicationAddress Type="SMTP">[email protected]</CommunicationAddress>
<InterchangePartyId IdCodeQualifier="ZZ">60900</InterchangePartyId>
<PartyId IdCodeQualifier="SVK" IdCodeResponsible="260">60900</PartyId>
</EDIFACTDetails>
</EDIFACTAddressing>
<NBSAddressing>
<NBSDetails Type="NBS">
<EnergyPartyId IdentifierType="EdielId">60900</EnergyPartyId>
</NBSDetails>
</NBSAddressing>
</EdielAddressing>
</Company>
</Market>
</CompanyListMessage>
</ExportPartyAddressingResponse>
我嘗試了以下 SQL 陳述句，只是為了首先從標題標記中獲取資訊...
WITH XMLNAMESPACES(DEFAULT N'http://www.ediel.no/Export')
SELECT
t.file_name, t.file_created_time,
h.value(N'(GeneratedAt/text())[1]', 'varchar(50)') h1,
h.value(N'@GeneratedAt', 'varchar(50)') h2
FROM
load.ediel_actors t
OUTER APPLY
t.xml_data.nodes('/ExportPartyAddressingResponse/CompanyListMessage') AS header(h)
我不明白為什么我的 SQL 不起作用。它是命名空間定義還是檢索值的代碼？
我正在使用 SQL Server 2019。
uj5u.com熱心網友回復：
兩個問題：
(1) : 您的 XPath 運算式是錯誤的 - 您還必須包含該/Header部分才能<Header>選擇 XML 元素
(2)：您的h1運算式是錯誤的 - 由于您試圖獲取XML 元素上的屬性值，因此您需要在第二行中使用該運算式。
所以試試這個：
WITH XMLNAMESPACES(DEFAULT N'http://www.ediel.no/Export')
SELECT
t.Col1,
h.value(N'@GeneratedAt', 'varchar(50)') HeaderGeneratedAt,
h.value(N'@SenderApplication', 'varchar(50)') SenderApplication
FROM
@datatbl t
OUTER APPLY
t.xmldata.nodes('/ExportPartyAddressingResponse/CompanyListMessage/Header') AS header(h)
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/364284.html
標籤：sql-server xml 查询语句 查询