CODEWITHSUNDEEP
c#sqlxml
Enceladus
Enceladus

Reputation: 41

Parsing Non-Standard XML Data from SQL Server

As part of a project I need to parse some values from some XML which doesn't seem to be standard XML. The XML is stored in SQL Server. I need to query the database and retrieve this XML, then in C# I need to get the value of the XCoord and YCoord fields. Can someone show me how this could be achieved using System.Xml?

If anyone knows a SQL Query to return these values from the XML data, that would do just as well.

<AdapterItem xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.enceladus.com/Data">
  <Attributes>
    <Attribute>
      <Name>Process ID</Name>
      <Value xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:string">1000</Value>
    </Attribute>
    <Attribute>
      <Name>Request</Name>
      <Value i:type="AdapterItem">
        <Attributes>
          <Attribute>
            <Name>Location</Name>
            <Value i:type="AdapterItem">
              <Attributes>
                <Attribute>
                  <Name>XCoord</Name>
                  <Value xmlns:d10p1="http://www.w3.org/2001/XMLSchema" i:type="d10p1:string">482557.53208923</Value>
                </Attribute>
                <Attribute>
                  <Name>YCoord</Name>
                  <Value xmlns:d10p1="http://www.w3.org/2001/XMLSchema" i:type="d10p1:string">240588.72462463</Value>
                </Attribute>
              </Attributes>
            </Value>
          </Attribute>
          <Attribute>
            <Name>Description</Name>
            <Value xmlns:d4p1="http://www.w3.org/2001/XMLSchema" i:type="d4p1:string">Some Description</Value>
          </Attribute>
        </Attributes>
      </Value>
    </Attribute>
  </Attributes>
</AdapterItem>

Thanks in advance!

Upvotes: 1

Views: 221

Answers (3)

Wagner DosAnjos
Wagner DosAnjos

Reputation: 6374

Here is C# solution:

var doc = new XmlDocument();

doc.LoadXml(columnValueFromSql);

Console.WriteLine("XCoord={0}, YCoord={1}",
    doc.SelectSingleNode("//Attribute[Name='XCoord']/Value").InnerText,
    doc.SelectSingleNode("//Attribute[Name='YCoord']/Value").InnerText);

/* Outputs:

XCoord=482557.53208923, YCoord=240588.72462463

*/

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

A SQL Server query would look like this:

with xmlnamespaces(default 'http://schemas.enceladus.com/Data')
select T.XMLCol.value('(//Attribute[Name = "XCoord"]/Value/text())[1]', 'varchar(20)') as XCoord,
       T.XMLCol.value('(//Attribute[Name = "YCoord"]/Value/text())[1]', 'varchar(20)') as YCoord
from YourTable as T

Upvotes: 0

Iain
Iain

Reputation: 6452

You could write some similar to this,

  WITH XMLNAMESPACES (http://www.w3.org/2001/XMLSchema' AS d4p1,
                      'http://schemas.enceladus.com/Data' AS message)

SELECT 
      CAST([XML_DATA] AS XML).value('(/AdapterItem/Attributes/Attribute[2]/Value/Attributes/Attribute[1]/Value/Attributes/Attribute[1]/Value)[1]', 'VARCHAR(20)') AS 'XCoord',
      CAST([XML_DATA] AS XML).value('(/AdapterItem/Attributes/Attribute[2]/Value/Attributes/Attribute[1]/Value/Attributes/Attribute[2])[1]', 'VARCHAR(20)') AS 'YCoord' FROM YOURTABLE

Upvotes: 0

Related Questions