Author Topic: "Repository.SQLQuery" operation returning xml  (Read 1630 times)

ff

  • EA Novice
  • *
  • Posts: 9
  • Karma: +0/-0
    • View Profile
"Repository.SQLQuery" operation returning xml
« on: December 10, 2009, 03:41:19 am »
Hi,

I'm using the "Repository.SQLQuery" operation to get the Object_ID from table t_Object.
The operations returns a string in xml format like this: <?xml version="1.0"?><EADATA version="1.0" exporter="Enterprise Architect"><Dataset_0><Data><Row><Object_ID>682</Object_ID></Row></Data></Dataset_0></EADATA>

How can I read the value of the Object_ID, i.e. 682, in this string?
I would like to use it for the Repository.GetElementbyID(int) operation.
I tried XMLReader but this reads xml from a file, not a string.

kr,
Flip


Geert Bellekens

  • EA Guru
  • *****
  • Posts: 8204
  • Karma: +193/-23
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: "Repository.SQLQuery" operation returning xml
« Reply #1 on: December 10, 2009, 04:31:30 am »
Flip,

It might be helpful to mention the programming language you are using  ;)

Geert

ff

  • EA Novice
  • *
  • Posts: 9
  • Karma: +0/-0
    • View Profile
Re: "Repository.SQLQuery" operation returning xml
« Reply #2 on: December 10, 2009, 04:34:40 am »
Of course! C# it is.

Thx,
Flip

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 8204
  • Karma: +193/-23
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: "Repository.SQLQuery" operation returning xml
« Reply #3 on: December 10, 2009, 04:54:53 am »
I think you're best of with the XDocument from the linq library.
That has a Parse(String) method that you can use.

The more knowledgeable programmers at work told me that XDocument is a lot faster then the XmlDocument, so I used that, and I have to admit, looping over all the nodes in a BIG (50000 nodes) xml string went very very fast.

Geert

ff

  • EA Novice
  • *
  • Posts: 9
  • Karma: +0/-0
    • View Profile
Re: "Repository.SQLQuery" operation returning xml
« Reply #4 on: December 10, 2009, 08:24:55 pm »
It works fine like this:

XmlDocument doc = new XmlDocument();
string xmlData = xmlstring;

doc.Load(new StringReader(xmlData));
String ea_guid = doc.DocumentElement.InnerText;
m_ele2 = Repository.GetElementByGuid(ea_guid);

kr,
Flip

Geert Bellekens

  • EA Guru
  • *****
  • Posts: 8204
  • Karma: +193/-23
  • Make EA work for YOU!
    • View Profile
    • Enterprise Architect Consultant and Value Added Reseller
Re: "Repository.SQLQuery" operation returning xml
« Reply #5 on: December 10, 2009, 09:02:54 pm »
Flip,

I just had a look at my code and I saw that I'm using the XmlDocument as well. Here is what my code looks like:

Code: [Select]
       public XmlDocument SQLQuery(string sqlQuery)
        {
            XmlDocument results = new XmlDocument();
            results.LoadXml(myRepository.SQLQuery(sqlQuery));
            return results;
        }

Later on I convert that to an XDocument to do fun stuff with like the query:

Code: [Select]
protected object[,] MarshalXmlDocumentToArray(XmlDocument xmlElements)
        {


            object[,] marshalledElements;
            //first check if there is data to be imported
            if (xmlElements.SelectSingleNode("//Row") != null)
            {
                //convert to XDocument for better performance
                XDocument xElements = XDocument.Load(xmlElements.CreateNavigator().ReadSubtree());

                var query = from xElem in xElements.Element("EADATA").Element("Dataset_0").Element("Data").Elements("Row")
                            select new ExportRow
                            {
                                ID = xElem.Element("ID").Value,
                                Type = xElem.Element("Type").Value,
                                Name = xElem.Element("Name").Value,
                                ImportanceLevel = xElem.Element("ImportanceLevel").Value,
                                Author = xElem.Element("Author").Value,
                                Created = xElem.Element("Created").Value,
                                Modified = xElem.Element("Modified").Value,
                                Documentation = xElem.Element("Documentation").Value,
                                ParentName = xElem.Element("ParentName").Value,
                                ParentID = xElem.Element("ParentID").Value,
                                QualifiedName = xElem.Element("QualifiedName").Value
                            };
                //query
                List<ExportRow> rows = query.ToList();

                //first add the headers
                marshalledElements = new object[rows.Count, 11];
                //headers now in the template excel file
                //marshalledElements = this.addHeaders(marshalledElements);
                //then add all rows
                for (int i = 0; i < rows.Count; i++)
                {
                    ExportRow row = rows[i];
                    marshalledElements[i , 0] = rows[i].ID;
                    marshalledElements[i , 1] = rows[i].Type;
                    marshalledElements[i , 2] = rows[i].Name;
                    marshalledElements[i , 3] = rows[i].ImportanceLevel;
                    marshalledElements[i , 4] = rows[i].Author;
                    DateTime createdDate;
                    if (DateTime.TryParse(rows[i].Created, out createdDate))
                    {
                        marshalledElements[i , 5] = createdDate;
                    }
                    else
                    {
                        //if the date cannot be parsed then just add the string as is
                        marshalledElements[i , 5] = rows[i].Created;
                    }
                    DateTime modifiedDate;
                    if (DateTime.TryParse(rows[i].Modified, out modifiedDate))
                    {
                        marshalledElements[i , 6] = modifiedDate;
                    }
                    else
                    {
                        //if the date cannot be parsed then just add the string as is
                        marshalledElements[i , 6] = rows[i].Modified;
                    }
                    marshalledElements[i , 7] = rows[i].QualifiedName;
                    if (rows[i].Documentation.Length < 255)
                    {
                        marshalledElements[i , 8] = rows[i].Documentation;
                    }
                    else
                    {
                        marshalledElements[i , 8] = rows[i].Documentation.Substring(0, 254);
                    }
                    marshalledElements[i , 9] = rows[i].ParentName;
                    marshalledElements[i , 10] = rows[i].ParentID;
                }

            }
            else
            {
                //no data, just returns the array with the headers
                marshalledElements = new string[1, 11];
                //marshalledElements = this.addHeaders(marshalledElements);
            }
            return marshalledElements;
        }

Geert