How to read child node in xml

Question

Does anyone know how to read child nodes of xml in sql ?

I am trying it hard but not able to get it ??

below is my sample 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>

and want to pull the details of PartnerID, SharedSecret, PartnerVersion, IntegrationVersion, ClientDBKey, RequestorID, ClientReferenceID, LanguageID

Below is my cursor

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

Answer 1

You have to do it this way:

 XElement element = XElement.Parse(XML);

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

UPDATED:

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

                   };

Working DEMO FIDDLE Example

Answer 2

0) you're question is not clear. however - if you want to read it in c# (assuming the data is retrieved from sql - or not)

1) You forgot to close the last tag.

2) Here's the full working example :

void Main()
{
    string g=@"<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>";

var doc = XDocument.Parse(g);
var dict = doc.Root.Elements();


                 var s=new [] {"SharedSecret","PartnerID","IntegrationVersion"/*...*/}.ToList();
                 foreach (var element in dict )
                {  
                    if ( element.Name=="Authentication")
                    {
                       s.ForEach(f=>Console.WriteLine (element.Element(f).Value));
                    }
                }
}

Result :

Provided By client
Provided By Client
Provided By Agile1
Provided By client
Provided By client
Provided By client