# Converting xml to MYSQL that reflects the "tag's embedding"?

**URL:** <https://www.ruby-forum.com/t/converting-xml-to-mysql-that-reflects-the-tags-embedding/111702>\
**Category:** Ruby\
**Created:** [August 9, 2007, 10:51pm UTC](https://www.ruby-forum.com/t/converting-xml-to-mysql-that-reflects-the-tags-embedding/111702 "2007-08-09T22:51:03Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![le\_colibri](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/ccd318/32.png) [@le\_colibri](https://www.ruby-forum.com/u/le_colibri)\
**Post date:** [August 9, 2007, 10:51pm UTC](https://www.ruby-forum.com/t/converting-xml-to-mysql-that-reflects-the-tags-embedding/111702/1 "2007-08-09T22:51:03Z")

</div>

Dear Ruby guru’s,

Can any of you tell me how to convert xml to SQL?

The tags of the text I need to convert are linguistic in nature. E.g.  
\</  
sentence\> and the like.

What I need, is a database that somehow reflects the embedding. Can  
this be done?

Kind regards,  
Robert.

---

<div class="post-metadata">

**Author:** ![le\_colibri](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/ccd318/32.png) [@le\_colibri](https://www.ruby-forum.com/u/le_colibri)\
**Post date:** [August 10, 2007, 11:29am UTC](https://www.ruby-forum.com/t/converting-xml-to-mysql-that-reflects-the-tags-embedding/111702/2 "2007-08-10T11:29:23Z")

</div>

Axel E. wrote:

> The finding of the brackets can be done using regular expressions.

Should one use a XML parser for this?

[http://www.ruby-doc.org/stdlib/libdoc/rexml/rdoc/index.html](http://www.ruby-doc.org/stdlib/libdoc/rexml/rdoc/index.html)

This doesn’t solve the problem of how to map a tree onto relational  
database tables, of course.

Lutz

---

<div class="post-metadata">

**Author:** ![le\_colibri](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/ccd318/32.png) [@le\_colibri](https://www.ruby-forum.com/u/le_colibri)\
**Post date:** [August 10, 2007, 11:53am UTC](https://www.ruby-forum.com/t/converting-xml-to-mysql-that-reflects-the-tags-embedding/111702/3 "2007-08-10T11:53:09Z")

</div>

le colibri wrote:

> Dear Ruby guru’s,
> 
> Can any of you tell me how to convert xml to SQL?
> 
> The tags of the text I need to convert are linguistic in nature. E.g.  
> \</  
> sentence\> and the like.
> 
> What I need, is a database that somehow reflects the embedding. Can  
> this be done?  
> I think what you need is a standard nested set structure, with the  
> various nodes having tags for their type and content. There’s a good  
> explanation here:  
> [http://dev.mysql.com/tech-resources/articles/hierarchical-data.html](http://dev.mysql.com/tech-resources/articles/hierarchical-data.html)

---

<div class="post-metadata">

**Author:** ![le\_colibri](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/ccd318/32.png) [@le\_colibri](https://www.ruby-forum.com/u/le_colibri)\
**Post date:** [August 11, 2007, 1:10am UTC](https://www.ruby-forum.com/t/converting-xml-to-mysql-that-reflects-the-tags-embedding/111702/4 "2007-08-11T01:10:46Z")

</div>

On Aug 10, 11:51 am, Alex Y. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> > this be done?
> 
> I think what you need is a standard nested set structure, with the  
> various nodes having tags for their type and content. There’s a good  
> explanation here:[http://dev.mysql.com/tech-resources/articles/hierarchical-data.html](http://dev.mysql.com/tech-resources/articles/hierarchical-data.html)
> 
> –  
> Alex

Thanks both Axel and Alex, I try out some of the suggestions.

Regards,  
Robert.

---

<div class="post-metadata">

**Author:** ![le\_colibri](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/ccd318/32.png) [@le\_colibri](https://www.ruby-forum.com/u/le_colibri)\
**Post date:** [August 10, 2007, 11:23am UTC](https://www.ruby-forum.com/t/converting-xml-to-mysql-that-reflects-the-tags-embedding/111702/5 "2007-08-10T11:23:01Z")

</div>

> Kind regards,  
> Robert.

Dear Robert,

I think what you need is to identify “brackets of text” starting with  
something like and ending with etc., then pass  
these brackets to some variable in Mysql.

For Ruby and MySql, see here:  
[http://www.kitebird.com/articles/ruby-mysql.html](http://www.kitebird.com/articles/ruby-mysql.html)

The finding of the brackets can be done using regular expressions.  
I’ve written some code below.  
You’d still need to choose some convenient variable name for the  
different  
types of brackets (sentence,word etc…)

Best regards,

Axel

* * *

class String  
def find\_positions(opening,closing)  
open\_p=[]  
closing\_p=[]  
temp=self.dup  
while opening.match(temp)  
ref=opening.match(temp)  
open\_p\<\<temp.index(ref[0])  
temp.sub!(ref[0],’ ‘\*ref[0].length)  
end  
temp=self.dup  
while closing.match(temp)  
ref=closing.match(temp)  
closing\_p\<\<temp.index(ref[0])  
temp.sub!(ref[0],’ '\*ref[0].length)  
end

```
# correctly associate opening and closing "brackets"
# check again, especially for nested brackets

brackets=[]
closing_p.each{|clb|
  temp=open_p.dup
  opb=(temp.delete_if{|y| y>clb}).max
  open_p=open_p-[opb]
  brackets<<[opb,clb]
}
return brackets.sort{|x,y| x[0]<=>y[0]}

```

end  
def find\_brackets\_text(opening,closing)  
pos\_array=self.find\_positions(opening,closing)  
text=[]  
p ‘positions of brackets in the text’  
p pos\_array  
pos\_array.each{|op,cl|  
p ‘opcl’  
p op  
p cl  
text\<\<self[op…cl]  
}  
return text  
end  
end  
a=“ ~~blabla~~~~ bla~~”  
c=a.find\_brackets\_text(/~~/,/\</s\>/)  
p c~~