Wednesday, 4 February 2015

inserting xml to sql server 12 using java



Following is my XML, that I have to store in SQL server db. that table has 2 columns. email and Xml.


XML:



<Users>
<user>
<FN>a</FN>
<LN>b</LN>
<ph>123</ph>
<email>abc</email>
</user>
<user>
<FN>c</FN>
<LN>d</LN>
<ph>456</ph>
<email>def</email>
</user>
<user>
<FN>r</FN>
<LN>p</LN>
<ph>321</ph>
<email>xyz</email>
</user>
</Users>


I split the XML using the following code.


public class XmlSplit {



public static void xmlSplit(File file) throws Exception {
XMLInputFactory if = XMLInputFactory.newInstance();
XMLStreamReader sr = if.createXMLStreamReader(new FileReader(file));
sr.nextTag(); // Advance to statements element

TransformerFactory tf = TransformerFactory.newInstance();
Transformer t = tf.newTransformer();
int counter = 1;
while(sr.nextTag() == XMLStreamConstants.START_ELEMENT) {
counter++;
File file2 = new File(counter + ".xml");
System.out.println(file2);
t.transform(new StAXSource(sr), new StreamResult(file2));
}
}

}


Now Instead of writing the split xmls into files, Can I create SQLXML objects directly or using a loop? And also I have to get email from the corresponding xml fragment and store it in a separate column. (Sometimes I don't have to split xml as there will be only 1 user and in other cases there will be 5-10 users in 1 xml file). (These xmls, I got as Strings in response to a web Service call and I have written these Strings into files).


No comments:

Post a Comment