title: XML specific line paring python
body: <p>I have an XML file sample , i have to build similar xml file .</p>
  <pre><code>&lt;datasource formatted-name='federated.1819qwi0hys5391dzxhl70o95li4' inline='true' source-platform='win' version='18.1' xmlns:user='http://www.tableausoftware.com/xml/user'&gt;
    &lt;connection class='federated'&gt;
      &lt;named-connections&gt;
        &lt;named-connection caption='Sample - Superstore' name='excel.1ew9u4t0tggb9315darmm0nfz2kb'&gt;
          &lt;connection class='excel' driver='' filename='C:/Users/XXXX/Downloads/Sample - Superstore.xls' header='yes' imex='1' password='' server='' /&gt;
        &lt;/named-connection&gt;
      &lt;/named-connections&gt;
  &lt;relation connection='excel.1ew9u4t0tggb9315darmm0nfz2kb' name='Custom SQL Query' type='text'&gt;SELECT [Orders$].[Category] AS [Category],&amp;#13;&amp;#10;[Orders$].[City] AS [City],&amp;#13;&amp;#10&lt;/relation&gt;
  </code></pre>
  <p>I have to read the relation connection sql query and write as below xml output dynamically.</p>
  <pre><code>     &lt;metadata-record class='column'&gt;
          &lt;remote-name&gt;Category&lt;/remote-name&gt;
          &lt;remote-type&gt;130&lt;/remote-type&gt;
          &lt;local-name&gt;[Category]&lt;/local-name&gt;
          &lt;parent-name&gt;[Custom SQL Query]&lt;/parent-name&gt;
       &lt;/metadata-record&gt;
  
        &lt;metadata-record class='column'&gt;
          &lt;remote-name&gt;City&lt;/remote-name&gt;
          &lt;remote-type&gt;130&lt;/remote-type&gt;
          &lt;local-name&gt;[City]&lt;/local-name&gt;
          &lt;parent-name&gt;[Custom SQL Query]&lt;/parent-name&gt;
       &lt;/metadata-record&gt;
  
  </code></pre>
  <p>I have build till relation connection which can be hard coded , but Meta data records should  be build dynamically by reading the sql query in relation connection . That is the expectation . Pasted the full code below</p>
  <pre><code>import xml.etree.cElementTree as et
  import pandas as pd
  colAtt[]
  
  datasource = et.Element(&quot;datasource&quot;)
  datasource.set(&quot;formatted-name&quot;,'federated.1819qwi0hys5391dzxhl70o95li4')
  datasource.set(&quot;inline&quot;,'True')
  datasource.set(&quot;source-platform&quot;,'win')
  datasource.set(&quot;version&quot;,'18.1')
  datasource.set(&quot;xmlns:user&quot;,'http://www.tableausoftware.com/xml/user')
  connection = et.SubElement(datasource, &quot;Connection&quot;)
  connection.set(&quot;Class&quot;, &quot;federated&quot;)
  namedconnections = et.SubElement(connection, &quot;named-connections&quot;)
  namedconnection = et.SubElement(namedconnections, &quot;namedconnection&quot;) 
  namedconnection.set(&quot;caption&quot;, 'Sample - Superstore')
  namedconnection.set(&quot;name&quot;, 'excel.1ew9u4t0tggb9315darmm0nfz2kb')
  connection=et.SubElement(namedconnection, &quot;connection&quot;)
  connection.set(&quot;class&quot;, 'excel')
  connection.set(&quot;driver&quot;, ' ')
  connection.set(&quot;filename&quot;, 'C:/Users/XXXX/Downloads/Sample - Superstore.xls')
  connection.set(&quot;header&quot;, 'yes')
  connection.set(&quot;imex&quot;, '1')
  connection.set(&quot;password&quot;, ' ')
  connection.set(&quot;server&quot;, ' ')
  
  relationconnection=et.SubElement(datasource, &quot;relation&quot;)
  relationconnection.set('Connection','excel.1ew9u4t0tggb9315darmm0nfz2kb')
  relationconnection.set('name ','Custom SQL Query')
  relationconnection.set('type','text')
  relationconnection.text='SELECT [Orders$].[Category] AS [Category],&amp;#13,&amp;#10,  [Orders$].[City] AS [City],&amp;#13,&amp;#10
    
  metadatarecords = et.SubElement(datasource, &quot;metadata-records&quot;)
  for x in datasource.findall('relation'):
           #print(x.tag,x.attrib,x.text)
           colAtt.append(x.text)
  </code></pre>
  <p>.</p>
  <p>But it is appending the whole text , i do not know how to split category and city separately . Can someone help please ??</p>