CODEWITHSUNDEEP
c#sql-serverlinqlinq-to-sqllinq-to-xml
y-user
y-user

Reputation: 78

Querying SQL table with LINQ and XElement

I don't know if this is possible to do but, I want to retrieve from a table in SQL a few fields plus a specific value inside an XML field in the table e.g.

MyTable(UserName varchar(50),XML_Response XML)

note: the data type in C# for value in field XML_Response is XElement

The XML inside field XML_Response bellow can be in two forms with | without value inside tag Update Result description:

<IntegrationResults>
  <UpdateResult>Failure</UpdateResult>
  <UpdateResultDescription>LeadId 2876474 not found</UpdateResultDescription>
</IntegrationResults>
<IntegrationResults>
    <UpdateResult>Success</UpdateResult>
    <UpdateResultDescription />
</IntegrationResults>

My query output I want to look like this (2 rows as examples) 

"My User Name","LeadId 83608 not found"
"My User Name 2",""

thank you in advance

Upvotes: 2

Views: 246

Answers (2)

y-user
y-user

Reputation: 78

thank you everyone, I was able to find a solution:

.select(x => new
{
   x.UserName,
   UpdateResult = x.MyTable.XML_Response.Element("UpdateResult").Value,
   UpdateResultDescription = x.MyTable.XML_Response.Element("UpdateResultDescription").Value,
});

Upvotes: 1

Kevin Suchlicki
Kevin Suchlicki

Reputation: 3145

As an alternative to what you are trying, you can use a C# command object and run the below SQL directly on the table:

SELECT UserName
      , IntegrationResults.n.value('UpdateResultDescription[1]','VARCHAR(200)') as UpdateResultDescription
  FROM  MyTable
        outer apply MyTable.XML_Response.nodes('/IntegrationResults') AS IntegrationResults(n);

Upvotes: 1

Related Questions