Unformatted XML to Flat XML

Question

 

I have a XML Document with multiple attribute values and multiple elements.

I have to convert that XML into flat XML File means exact tree structure for each row (tag)

For example my XML is like below.

<ROOT>

                <RECORD>

                                <A>abcd</A>

                                <B>                                    // Multiple Elements for single tag

<B>abcd</B>

                                </B>     

</B>                                      // Multiple Elements for single tag

<B>efgh</B>

                                </B>                     

<B>                                        // Multiple Elements for single tag

<B>ijkl</B>

                                <B>

<C  C1= “abcd”  C1= “efgh”  />                   // Multiple Attribute values

<D D1= “abcd” D1”= “efgh” D1=”ijkl”/>   // Multiple Attributes values

                </RECORD>

</ROOT>

These type of Not well formatted XML Files I have. But I need to convert into flat XML File like below by using XSLT or X-PATH.

<ROOT>

                <RECORD>

                                <A>abcd</A>

<B>abcd</B>

<C>abcdefgh</C>

<D>abcdefghijkl</D>

                </RECORD>

                <RECORD>

                                <A>abcd</A>

<B> efgh </B>

<C>abcdefgh</C>

<D>abcdefghijkl</D>

                </RECORD>

                <RECORD>

                                <A>abcd</A>

<B> ijkl </B>

<C>abcdefgh</C>

<D>abcdefghijkl</D>

                </RECORD>

</ROOT>

Answer 1

 

I have a XML Document with multiple attribute values and multiple elements.

I have to convert that XML into flat XML File means exact tree structure for each row (tag)

For example my XML is like below.

<ROOT>

                <RECORD>

                               <A>abcd</A>

                               <B>                                    // Multiple Elements for single tag

                                    <B>abcd</B>

                               </B>     

                    </B>                                      // Multiple Elements for single tag

                                    <B>efgh</B>

                               </B>                     

                    <B>                                        // Multiple Elements for single tag

                                       <B>ijkl</B>

                                 <B>

                     <C  C1= “abcd”  C1= “efgh”  />                   // Multiple Attribute values

                     <D D1= “abcd” D1”= “efgh” D1=”ijkl”/>   // Multiple Attributes values

                </RECORD>

</ROOT>

These type of Not well formatted XML Files I have. But I need to convert into flat XML File like below by using XSLT or X-PATH.

<ROOT>

                <RECORD>

                                  <A>abcd</A>

                       <B>abcd</B>

                      <C>abcdefgh</C>

                       <D>abcdefghijkl</D>

                </RECORD>

                <RECORD>

                                <A>abcd</A>

                     <B> efgh </B>

                   <C>abcdefgh</C>

                   <D>abcdefghijkl</D>

                </RECORD>

                <RECORD>

                             <A>abcd</A>

                 <B> ijkl </B>

                 <C>abcdefgh</C>

                 <D>abcdefghijkl</D>

                             </RECORD>

</ROOT>

Answer 2

Unfortunately, the sample provided is not a valid xml structure (e.g. missing end tags, duplicate attribute names a.s.o.)

Is this really the structure you're faced with?

Answer 3

Hi Ram.BI,

Could you please elaborate a bit? Do you want to transform XML data using an Extensible Stylesheet Language for Transformations (XSLT) in SQL Server?

I think SQL Server and Transact-SQL is no suitble to handle such kind of task. I would suggest you perform the transformation in .NET or create a SQL CLR function in SQL Server.

Please see:

http://www.xmlfiles.com/articles/cynthia/xslt/default.asp
http://blogs.msdn.com/b/mrorke/archive/2005/06/28/433471.aspx
http://msdn.microsoft.com/en-us/library/system.xml.xsl.xsltransform.aspx 

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Get or Request Code Sample from Microsoft
If you have any feedback, please tell us.

Answer 4

Is there any particular reason for posting exactly the same question instead of answering the question already asked over there?

Answer 5

i have multi level XML Files like below:

<ROOT>
      <RECORD>
             <A>Value1</A>
             <B>Value2</B>
             <B>Value3</B>
             <C>Value4</C>
             <D>Value5</D>
             <E>Value6</E>
      </RECORD>
      </RECORD>
             <A>Value7</A>
             <B>Value8</B>
             <C>Value9</C>
             <D>Value10</D>
             <E>Value11</E>
      </RECORD>
<ROOT>

I have to convert this file into flat XML File like below::
<ROOT>
     <RECORD>
            <A>Value1</A>
             <B>Value2</B>
             <C>Value4</C>
             <D>Value5</D>
             <E>Value6</E>
     </RECORD>
     <RECORD>
            <A>Value1</A>
             <B>Value3</B>
             <C>Value4</C>
             <D>Value5</D>
             <E>Value6</E>
     </RECORD>
     </RECORD>
             <A>Value7</A>
             <B>Value8</B>
             <C>Value9</C>
             <D>Value10</D>
             <E>Value11</E>
      </RECORD>
 </ROOT>