Translate

Friday, March 1, 2013

XML - Using XQuery to Transform an XML File

What I learned in the Introduction to Databases came in handy. I had a log file in XML that I needed to query on to get some information. Unfortunately because of the structure of the file I wasn't able to query on it to get the data I needed and I wasn't able to import it into Access. Using XQuery, I transformed the file into a format that I could import into Access.

Here is a section of the original XML file:

<BB>
 <Content DateTime="2/28/2013 8:00:16 AM" Account="INTERNEWS\lkuncan" Content="Deleted" />
  <Content DateTime="2/28/2013 8:00:16 AM" Account="INTERNEWS\cnedlin" Content="Deleted" />
  <Content DateTime="2/28/2013 8:00:16 AM" Account="INTERNEWS\cdfield" Content="Deleted" />
  <Content DateTime="2/28/2013 8:00:16 AM" Account="INTERNEWS\ograw" Content="Deleted" />


This is the format I needed it to be in:

<bRec>
   <Account>INTERNEWS\lkuncan</Account>
   <Content>Deleted</Content>
</bRec>
<bRec>
   <Account>INTERNEWS\cnedlin</Account>
   <Content>Deleted</Content>
</bRec>
<bRec>
   <Account>INTERNEWS\cdfield</Account>
   <Content>Deleted</Content>
</bRec>
<bRec>
   <Account>INTERNEWS\ograw</Account>
   <Content>Deleted</Content>
</bRec>


This is the XQuery Expression I used to transform the XML file:

for $b in doc("Blog.xml")/BB/Content 
return
<bRec>
  <Account> { $b/data(@Account) } </Account>
  <Content>{ $b/data(@Content) }</Content>
</bRec>

I used Kernow to run the expression and get my results.