how to select a particular node from xml file using node's name and a specific attribute value?

Question

i have a MySQL DB in which i have created a table with a longtext column in which i store xml files as a string so i am asking if it is possible to get nodes using their names and a specific attribute value! here is an example of xml i deal with :

<?xml version="1.0" encoding="ISO-8859-1" standalone="yes" ?>
<HWData>
  <Header time="2013-05-29T13:39:34" uploaded="true" version="1.0" />
  <NE vendorName="Nokia Siemens Networks" NEId="WBTS-431">
    <EQHO vendorName="Nokia Siemens Networks" equipmentHolderId="173" >
        <UNIT vendorName="N" unitId="16" />
        <UNIT vendorName="NOKIA SIEMENS NETWORKS" unitId="225" />
    </EQHO>
    <EQHO vendorName="NSN" equipmentHolderId="40192" >
        <UNIT vendorName="AR" unitId="40267" />
    </EQHO>
  </NE>
  <NE vendorName="Nokia Siemens Networks" NEId="WBTS-261">
    <EQHO vendorName="Nokia Siemens Networks" equipmentHolderId="132" >
      <EQHO vendorName="Nokia Siemens Networks" equipmentHolderId="132-1">
        <UNIT vendorName="NN" unitId="1621" />
      </EQHO>
    </EQHO>
  </NE>
</HWData>

is possible to use "EQHO" ( node name ) and "NE-RNC-4/DN:NE-WBTS-4204/EQHO-173" ( value of attribute MOID ) to get this as result using SQL query ! :

   <NE vendorName="Nokia Siemens Networks" NEId="WBTS-261">
    <EQHO vendorName="Nokia Siemens Networks" equipmentHolderId="132" >
      <EQHO vendorName="Nokia Siemens Networks" equipmentHolderId="132-1">
        <UNIT vendorName="NN" unitId="1621" />
      </EQHO>
    </EQHO>
   </NE>

can someone put me on the right way to achieve that..any other suggestions or example will be appreciated. thanks in advance

Search for java xml parsing using a search engine of your choice. — fabian
– fabian, Commented May 21, 2015 at 12:32
hi @fabian i am asking if there is way to get the resault using SQL query !! — Hen
– Hen, Commented May 21, 2015 at 13:05
Just to clarify you want to pull the any xml node using mysql without knowing the structure ? Do you know the node names ? — Kenneth Clark
– Kenneth Clark, Commented May 21, 2015 at 13:43
There is an answer here to MySQL stackoverflow.com/questions/21055122/… .. This however may not suit your needs as it requires changes to the MySQL server — Kenneth Clark
– Kenneth Clark, Commented May 21, 2015 at 13:56

har07 · Accepted Answer · 2015-05-23 12:58:24Z

1

MySQL has a very limited functionality when it comes to querying XML data. There is ExtractValue() function which can be used to get node/attribute value from XML document, but it can't return subtree.

Your best bet might be using xpath in the host language of your choice instead. Xpath to get particular node by node's name and specific attribute value according to the example in question would be as follow :

//EQHO[@MOID="NE-RNC-4/DN:NE-WBTS-4204/EQHO-173"]

Above xpath return all <EQHO> elements anywhere in the XML document which have MOID attribute value equals "NE-RNC-4/DN:NE-WBTS-4204/EQHO-173". You can even achieve the same without knowing the attribute name, if you want, by replacing @MOID with @*.

answered May 23, 2015 at 12:58

har07

89.5k12 gold badges87 silver badges143 bronze badges

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

2 Comments

Hen Over a year ago

hi @har07 thanks for replying i forgot to say that i am dealing with xml files that i don't know their structure in advance so xpath will not help in my case but i have found another solution to do that..i'll post it as response

har07 Over a year ago

@HayderHenchiri I think xpath still applicable. You can achieve the same without knowing the element name with xpath too : //*[@MOID="NE-RNC-4/DN:NE-WBTS-4204/EQHO-173"]

Hen · Accepted Answer · 2015-05-24 00:51:23Z

i used this method to get the node as a string and it is working fine :

public static String NodeToString(String file,String moid) throws   SAXException, IOException, ParserConfigurationException{
    String stringNode="";


    InputStream in = new ByteArrayInputStream(file.getBytes("ISO-8859-1"));
    Document doc = DocumentBuilderFactory.newInstance().newDocumentBuilder().parse(in);
    NodeList nodeList = doc.getElementsByTagName("*");
    for (int i = 0; i < nodeList.getLength(); i++) {
        if(nodeList.item(i) instanceof Element && ((Element)nodeList.item(i)).getAttribute("MOID").equalsIgnoreCase(moid)){

    try
    {

      // Set up the output transformer
      TransformerFactory transfac = TransformerFactory.newInstance();
      Transformer trans = transfac.newTransformer();
      trans.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "yes");
      trans.setOutputProperty(OutputKeys.INDENT, "yes");

      // Print the DOM node

      StringWriter sw = new StringWriter();
      StreamResult result = new StreamResult(sw);
      DOMSource source = new DOMSource(nodeList.item(i));
      trans.transform(source, result);
      stringNode = sw.toString();

      //System.out.println(xmlString);
    }
    catch (TransformerException e)
    {
      e.printStackTrace();
    }       


        }
}
    return stringNode;

}

Kenneth Clark · Accepted Answer · 2015-05-21 12:42:38Z

0

You can use xpath.

/HWData/NE/EQHO[@MOID="NE-RNC-4/DN:NE-WBTS-4204/EQHO-173"]

Sample java code

  InputSource is = new InputSource(new StringReader(xml)) ;
  Document doc = builder.parse(is);
  XPathFactory xPathfactory = XPathFactory.newInstance();
  XPath xpath = xPathfactory.newXPath();
  XPathExpression expr = xpath.compile("/HWData/NE/EQHO[@MOID=\"NE-RNC-4/DN:NE-WBTS-4204/EQHO-173\"]");
  NodeList nl = (NodeList) expr.evaluate(doc, XPathConstants.NODESET);
  for (int i = 0; i < nl.getLength(); i++)
  {
    Node node =  nl.item(i);
    System.out.println(nl.item(i).getNodeName());
  }

edited May 21, 2015 at 12:42

answered May 21, 2015 at 12:34

Kenneth Clark

1,7752 gold badges14 silver badges27 bronze badges

1 Comment

Hen Over a year ago

hi @Kenneth XPath will not work in my case because i am dealing with xml files that i don't know their structure in advance,, the xml that i have posted is just an example

Collectives™ on Stack Overflow

how to select a particular node from xml file using node's name and a specific attribute value?

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related