Parsing Non-Standard XML Data from SQL Server

Question

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!

By "nonstandard", do you mean "poorly formed and does not parse"? — Brian Warshaw
– Brian Warshaw, Commented Jan 21, 2014 at 17:06
The XML is well formed but I was not familiar with its layout. You could say <Attribute name="XCoord">000.000</Attribute> but I realise that the value requires additional attributes of its own, such as 'type'. — Enceladus
– Enceladus, Commented Jan 22, 2014 at 2:03
Thanks for clarifying. I think it would be/would have been better to not say "nonstandard", because it is potentially misleading. But glad you got an answer, at any rate! — Brian Warshaw
– Brian Warshaw, Commented Jan 22, 2014 at 13:08

Wagner DosAnjos · Accepted Answer · 2014-01-21 18:17:26Z

1

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

*/

answered Jan 21, 2014 at 18:17

Wagner DosAnjos

6,3841 gold badge18 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Enceladus Over a year ago

Thanks! That works great. This works if I remove my namespace on line 1.

Iain · Accepted Answer · 2014-01-21 17:16:08Z

0

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

answered Jan 21, 2014 at 17:16

Iain

6,5022 gold badges33 silver badges51 bronze badges

2 Comments

JLRishe Over a year ago

I think you're assuming things about the XML structure that may not always be true. You should probably be matching on the attribute names rather than their positions.

Enceladus Over a year ago

See top comment. It didn't seem right at first but the structure is designed like that.

Mikael Eriksson · Accepted Answer · 2014-01-21 17:53:52Z

0

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

answered Jan 21, 2014 at 17:53

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

1 Comment

Enceladus Over a year ago

Awesome. This works on the SQL side. I'd give you a vote up but haven't got sufficient reputation! Thanks.

Collectives™ on Stack Overflow

Parsing Non-Standard XML Data from SQL Server

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related