CODEWITHSUNDEEP
c#xml
salman
salman

Reputation: 565

getting xml attributes from SQL

For this xml (in a SQL 2005 XML column): 

<doc> 
 <a>1</a> 
 <b ba="1" bb="2" bc="3" /> 
 <c bd="3"/> 
<doc>

I'd like to be able to retrieve the names of the attributes (ba, bb, bc, bd) rather than the values inside using C# windows form application.

Upvotes: 2

Views: 359

Answers (2)

marc_s
marc_s

Reputation: 754598

Try something like this - the first method will load the XML string from the database (adjust the connection string and query to your own database, server, table, column names), and the second method will parse the XML string loaded from the database into a list of attribute names based on the answer you got for your previous question:

 static void Main(string[] args)
    {
        string xmlContent = GrabStringFromDatabase(1);
        List<string> attributeNames = ParseForAttributeNames(xmlContent);

        Console.WriteLine("Your XML attributes are: {0}", string.Join(",", attributeNames.ToArray()));
    }

    private static string GrabStringFromDatabase(int ID)
    {
        string result = string.Empty;
        string connection = "server=(local);database=test;integrated security=SSPI";
        string query = "SELECT XmlContent FROM dbo.TestXml WHERE ID = @ID";

        using(SqlConnection _con = new SqlConnection(connection))
        using (SqlCommand _cmd = new SqlCommand(query, _con))
        {
            _cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ID;

            _con.Open();
            result = _cmd.ExecuteScalar().ToString();
            _con.Close();
        }

        return result;
    }

    private static List<string> ParseForAttributeNames(string xmlContent)
    {
        List<string> attributeNames = new List<string>();

        XDocument xmlDoc = XDocument.Parse(xmlContent);

        var nodeAttrs = xmlDoc.Descendants().Select(x => x.Attributes());

        foreach (var attrs in nodeAttrs)
        {
            foreach (var attr in attrs)
            {
                attributeNames.Add(attr.Name.LocalName);
            }
        }

        return attributeNames;
    }

Upvotes: 1

BeemerGuy
BeemerGuy

Reputation: 8269

There are two ways to do this in your case:

  • If you can create stored procedures on your SQL server:
    You can create a stored procedure that disassembles the XML into columns, and easily SELECT them and have the C# app handle it as if it were a table with columns and rows.

  • If you can't:
    You can disassemble the XML in C# using SqlXml from the System.Xml namespace.

Both ways are explained with examples very well here:
http://msdn.microsoft.com/en-us/library/ms345117(SQL.90).aspx#sql2k5xml_topic4

Upvotes: 0

Related Questions