如何在xml中读取子节点

debugcn 发表于 Dev

XamDev

有谁知道如何在sql中读取xml的子节点？

我正在努力尝试，但无法获得它？

以下是我的示例XML

<RequisitionRequest>
<Authentication>
  <PartnerID>Provided By Client</PartnerID>
  <SharedSecret>Provided By client</SharedSecret>
  <PartnerVersion>client</PartnerVersion>
  <IntegrationVersion>Provided By Agile1</IntegrationVersion>
  <ClientDBKey>Provided By Agile1</ClientDBKey>
  <RequestorID>J234</RequestorID>
</Authentication>

<JobPosting>
  <ClientReferenceID>C112</ClientReferenceID>
  <Requisition>
    <LanguageID>1</LanguageID>
  </Requisition>
</JobPosting>

<Authentication>
  <PartnerID>Provided By client</PartnerID>
  <SharedSecret>Provided By client</SharedSecret>
  <PartnerVersion>client</PartnerVersion>
  <IntegrationVersion>Provided By client</IntegrationVersion>
  <ClientDBKey>Provided By client</ClientDBKey>
  <RequestorID>33234</RequestorID>
</Authentication>

<JobPosting>
  <ClientReferenceID>C100</ClientReferenceID>
  <Requisition>
    <LanguageID>2</LanguageID>
  </Requisition>
</JobPosting>
<RequisitionRequest>

并想提取PartnerID，SharedSecret，PartnerVersion，IntegrationVersion，ClientDBKey，RequestorID，ClientReferenceID，LanguageID的详细信息

下面是我的光标

declare cur cursor local fast_forward for
SELECT
COALESCE([Table].[Column].value('( ./ Authentication/PartnerID)[1]', 'int'),0) as 'PartnerID',
[Table].[Column].value('(./ Authentication/SharedSecret) [1]', 'varchar(max)') as '  SharedSecret',               
[Table].[Column].value(' (./ Authentication/PartnerVersion)[1]', 'varchar(max)') as '          PartnerVersion',
[Table].[Column].value('( ./ Authentication/IntegrationVersion) [1]', 'varchar(max)') as ' IntegrationVersion',
[Table].[Column].value(' ( ./ JobPosting / ClientReferenceID) [1]', 'varchar(max)') as ' ClientReferenceID',
[Table].[Column].value('(./ Authentication/ClientDBKey) [1]', 'varchar(max)') as ' ClientDBKey ',
[Table].[Column].value('(./ Authentication/RequestorID) [1]', 'varchar(max)') as ' RequestorID ',
[Table].[Column].value('( ./ Authentication/JobPosting/Requisition/LanguageID)[1]', 'int'),0) as    'LanguageID',
FROM @input.nodes('/ RequisitionRequest') as [Table]([Column])

open cur
while 1=1
begin
fetch cur into @PartnerID, @SharedSecret,    @PartnerVersion,@IntegrationVersion,@ClientReferenceID,@DBKey,@RequestorID,@LanguageID
if @@fetch_status <> 0 break
print @PartnerID
print @SharedSecret
print @PartnerVersion
print @IntegrationVersion
print @ClientReferenceID
print @DBKey
print @RequestorID
print @LanguageID
end
close cur
deallocate cur

埃桑·贾杰德（Ehsan Sajjad）

您必须这样做：

 XElement element = XElement.Parse(XML);

var Result = from a in element.Descendants("Authentication")
             select new
                   {
                     PartnerID = a.Element("PartnerID").Value,
                     ...............................
                     .............................
                   };

更新：

var Result = from a in element.Descendants("Authentication")
             from b in element.Descendants("JobPosting")
             from c in b.Descendants("Requisition")
             select new
                   {
                     PartnerID = a.Element("PartnerID").Value,
                     SharedSecret = a.Element("SharedSecret").Value,
                     PartnerVersion = a.Element("PartnerVersion").Value,
                     IntegrationVersion = a.Element("IntegrationVersion").Value,
                     ClientDBKey = a.Element("ClientDBKey").Value,
                     RequestorID = a.Element("RequestorID").Value,
                     ClientReferenceID = b.Element("ClientReferenceID").Value,
                     LanguageID = c.Element("LanguageID").Value

                   };

工作演示场示例

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。