Link to home
Create AccountLog in
Avatar of jasont09
jasont09

asked on

C# Parsing XML Response - Cant find the column - but I can see it

Hi

I am trying to get some of the columns from the xml response into strings so I can then send them to the db.

Most of the columns work just fine. Then I get to the column 'Feature' which i believe is in 'ItemAttributes'

There are multiple rows of 'Feature'

Either way I get the following error :(
Column 'Feature' does not belong to table ItemAttributes.

please can someone show me how to get the columns feature into either 1 string or multiple strings.

I have attached the xml response file.

I also have problems that sometimes the EditorialReviews column does not show for every item, don't think I've gone the best way around checking if it exists.. so any pointers would be appreciated.

string s1 = "";
            string s2 = "";
            string s3 = "";
            string s4 = "";
            string s5 = "";
            string s6 = "";
            string s7 = "";
            string s8 = "";
            string s9 = "";
            string s10 = "";
            
           DS.WriteXml(Response.OutputStream, XmlWriteMode.IgnoreSchema);
            //Response.Write("Total Results:");
           Response.Write("</br>");
           Response.Write(DS.Tables["Item"].Rows[0]["ASIN"].ToString());
           s1 = DS.Tables["Item"].Rows[0]["ASIN"].ToString();
           Response.Write("</br>");
           Response.Write(DS.Tables["LargeImage"].Rows[0]["URL"].ToString());
           s2 = DS.Tables["LargeImage"].Rows[0]["URL"].ToString();
           Response.Write("</br>");
           Response.Write(DS.Tables["ItemAttributes"].Rows[0]["Brand"].ToString());
           s3 = DS.Tables["ItemAttributes"].Rows[0]["Brand"].ToString();
           Response.Write("</br>");
           Response.Write(DS.Tables["ItemAttributes"].Rows[0]["EAN"].ToString());
           s4 = DS.Tables["ItemAttributes"].Rows[0]["EAN"].ToString();
           Response.Write("</br>");
           if (DS.Tables["ItemAttributes"].ToString().Contains("MPN"))
           {
               Response.Write(DS.Tables["ItemAttributes"].Rows[0]["MPN"].ToString());
               s5 = DS.Tables["ItemAttributes"].Rows[0]["MPN"].ToString();
           }
           Response.Write("</br>");
            if (DS.Tables["ItemAttributes"].ToString().Contains("Model"))
            {
                Response.Write(DS.Tables["ItemAttributes"].Rows[0]["Model"].ToString());
                s6 = DS.Tables["ItemAttributes"].Rows[0]["Model"].ToString();
            }
            Response.Write("</br>");
            //if (DS.Tables["ItemAttributes"].ToString().Contains("Feature"))
           // {
                Response.Write("</br>");
                Response.Write("--**--");
                Response.Write("</br>");
                Response.Write(DS.Tables["ItemAttributes"].Rows[0]["Feature"].ToString());
                s7 = DS.Tables["ItemAttributes"].Rows[0]["Feature"].ToString();
                Response.Write("</br>");
                Response.Write("--**--");
                Response.Write("</br>");
           // }
           Response.Write("</br>");
           Response.Write(DS.Tables["ItemAttributes"].Rows[0]["Title"].ToString());
           s8 = DS.Tables["ItemAttributes"].Rows[0]["Title"].ToString();
           Response.Write("</br>");
           Response.Write(DS.Tables["LowestNewPrice"].Rows[0]["FormattedPrice"].ToString());
           s9 = DS.Tables["LowestNewPrice"].Rows[0]["FormattedPrice"].ToString();
           Response.Write("</br>");

           if (DS.Tables["Item"].ToString().Contains("EditorialReviews"))
           {
               if (DS.Tables["EditorialReview"].Rows.Count > 0)
               {
                   Response.Write(DS.Tables["EditorialReview"].Rows[0]["Content"].ToString());
                   s10 = DS.Tables["EditorialReview"].Rows[0]["Content"].ToString();
               }
               Response.Write("</br>");
           }

Open in new window

amaz.xml
Avatar of james-ct16
james-ct16
Flag of Australia image

Howdy

You appear to be using the xml using a dataset, the reason you cannot see the features values is because it will be contained in a different table linked by foreign key. I would suggest that you dispense with the dataset and read the values you want directly from the xml. Here is an example

XmlDocument doc = new XmlDocument();
            doc.LoadXml(this.LoadXml());

            /*
             The following is the section we are interested in
            <Binding>Sports</Binding>
<Brand>Ultrafit</Brand>
<Department>unisex-adult</Department>
<EAN>4046228110226</EAN>
<Label>Ultrafit</Label>
<Manufacturer>Ultrafit</Manufacturer>
<Model>331100000022</Model>
<MPN>331100000022</MPN>
<PartNumber>331100000022</PartNumber>
<ProductGroup>Sports</ProductGroup>
<ProductTypeName>SPORTING_GOODS</ProductTypeName>
<Publisher>Ultrafit</Publisher>
<SKU>331100000022</SKU>
<Studio>Ultrafit</Studio>
<Title>Ultrafit F-Bike</Title>*/

            // find the item attributes
            XmlElement itemAttributes = doc["ItemLookupResponse"]["Items"]["Item"]["ItemAttributes"];


            System.Diagnostics.Debug.WriteLine(itemAttributes["Brand"].InnerText);
            System.Diagnostics.Debug.WriteLine(itemAttributes["Department"].InnerText);
            System.Diagnostics.Debug.WriteLine(itemAttributes["EAN"].InnerText);
            System.Diagnostics.Debug.WriteLine(itemAttributes["Label"].InnerText);
            System.Diagnostics.Debug.WriteLine(itemAttributes["Manufacturer"].InnerText);
            System.Diagnostics.Debug.WriteLine(itemAttributes["SKU"].InnerText);

            // loop through and find all features
            XmlNodeList featureNodes = itemAttributes.GetElementsByTagName("Feature");
            if (featureNodes != null)
            {
                foreach (XmlNode node in featureNodes)
                {
                    System.Diagnostics.Debug.WriteLine(node.InnerText);
                }
            }

Open in new window


So I have created an xml document and read the xml into it as a string (convenience for the sake of the example it could come direct from the stream).

I then navigate to the node I am looking for, again here you should be checking that the nodes exist. This example then simply dumps the values to the output window in VS but you would set the variables here.

Hope that helps
Avatar of jasont09
jasont09

ASKER

this looks very good and makes clear reading thanks :)

please can you show me how I get it direct from the stream?

at the moment it is creating the data set

DataSet DS = GetData(requestUrl);

DS.WriteXml(Response.OutputStream, XmlWriteMode.IgnoreSchema);
the other part is this

 DataSet GetData(string signedurl)
   {
    try
     {
            //Create a request object using signed URL.
        WebRequest request = HttpWebRequest.Create(signedurl);
            //Get response in a stream
         Stream responseStream = request.GetResponse().GetResponseStream();

        DataSet DS = new DataSet();
            //Read returned resonpse stream into a dataset.
            //Note: You can also use XMLDocument element here to read response.
   DS.ReadXml(responseStream);
   //DS.WriteXml(Response.OutputStream, XmlWriteMode.IgnoreSchema);
          responseStream.Close();

          return DS;
        }

Open in new window

Howdy

Getting the xml from the stream is very easy, in the following example I load the Xml Document from the bbc news rss feed, obviously change this as required.

WebRequest wr = HttpWebRequest.Create("http://feeds.bbci.co.uk/news/rss.xml");
 doc.Load(wr.GetResponse().GetResponseStream());

Open in new window


As you can see there is nothing to it. The Load overload also will accept filepaths, text readers and xml readers.

Hope that helps
I'm being totally stupid here some where

 The name 'wr' does not exist in the current context


XmlDocument doc = new XmlDocument();
            doc.Load(wr.GetResponse().GetResponseStream());

then updated this part

 DataSet GetData(string signedurl)
   {
    try
     {
            //Create a request object using signed URL.
        WebRequest request = HttpWebRequest.Create(signedurl);
            //Get response in a stream
         Stream responseStream = request.GetResponse().GetResponseStream();
         WebRequest wr = HttpWebRequest.Create(signedurl);
                DataSet DS = new DataSet();
                   //Read returned resonpse stream into a dataset.
            //Note: You can also use XMLDocument element here to read response.
   DS.ReadXml(responseStream);
             responseStream.Close();

          return DS;
        }

Open in new window

I appreciate your time and help here.. its now 1am here and my eyes are bleeding lol so off heading for bed

if you can guide me on that last past, it will certainly help me.

many thanks
ASKER CERTIFIED SOLUTION
Avatar of james-ct16
james-ct16
Flag of Australia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thank you so much for your time and effort. This works perfectly and I now have a much better understanding of the logic.