Parsing XML through Stored Procedures

  • Hi,

    I need to read a XML file from a specific location from a stored proecedure, parse it and store into table columns.

    =================

    ?xml version="1.0"?>

    <feed1>

       <Table name="Table1">

             <column name ="col1">

                <coldata>Col1Sample1</coldata>

                <coldata>Col1Sample2</coldata>

                <coldata>Col1Sample3</coldata>

             </column>

            <column name ="col2">

               <coldata>Col2Sample1</coldata>

               <coldata>Col2Sample2</coldata>

               <coldata>Col2Sample3</coldata>

            </column>

            <column name ="col3">

               <coldata>Col3Sample1</coldata>

               <coldata>Col3Sample2</coldata>

               <coldata>Col3Sample3</coldata>

             </column>

        </Table>

    </feed1>

    ==================This would be my XML file format.

    Rather than inserting the whole XML file into a single table column, My stored procedure should read this file and also parse it. In the parsing process, when a node like <table> is encountered, a create table stmt shld be intiatated, and similary when node like <column> is encountered, the value in this node will be the column header and so on.

    The end result would be that, the stored procedure should end up creating a table structure in Database along with data as defined in XML File.

    Table1

    =========================

    Col1                               Col2                           Col3

    Col1Sample1                 Col2Sample1                  Col3Sample1  

    Col1Sample2                 Col2Sample2                  Col3Sample2     

    Col1Sample3                 Col2Sample3                  Col3Sample2  

    ======================

     

    Any help would be appreciated.

     

  • Your going to have to restructure your xml.

    as you have under column you have a element named coldata when parsing xml since the names are the same sql does not know which one to choose , so it choses the first.

    otherwise you can do like this.

    declare @XML_Document varchar(4000)

    declare @Handle int

    set @XML_Document = '<?xml version="1.0"?>

    <feed1>

       <Table name="Table1">

             <column name="col1">

                <coldata>Col1Sample1</coldata>

                <coldata1>Col1Sample2</coldata1>

                <coldata2>Col1Sample3</coldata2>

             </column>

            <column name="col2">

               <coldata>Col2Sample1</coldata>

               <coldata1>Col2Sample2</coldata1>

               <coldata2>Col2Sample3</coldata2>

            </column>

            <column name="col3">

               <coldata>Col3Sample1</coldata>

               <coldata1>Col3Sample2</coldata1>

               <coldata2>Col3Sample3</coldata2>

             </column>

        </Table>

    </feed1>'

      exec sp_xml_preparedocument @Handle output, @XML_Document

    select *

    from openxml(@Handle,'/feed1/Table/column',2)

    with (ColumnName varchar(50) '../@name',

          ColName varchar(50) '@name',

          coldata varchar(50) ,

          coldata1 varchar(50) ,

          coldata2 varchar(50)

          )

      exec sp_xml_removedocument @Handle

     

  • Thanks for your response. It was helpful.

    However, as indicated, the XML file needs to be programatically loaded from its folder path. This procedure would be scheduler based, and would be executed automatically. So, in your code snippet, instead of the following line

    "set @XML_Document = '<?xml version="1.0"?>

    <feed1>.........................."

    this part should pick up the XML file from a specified file location. Eg: "c:\feedxml\Test1.XML".

    Assuming the file is dynamically loaded...

    In the following lines

    "-----------select *

    from openxml(@Handle,'/feed1/Table/column',2)

    with (ColumnName varchar(50) '../@name',

          ColName varchar(50) '@name',

          coldata varchar(50) ,

          coldata1 varchar(50) ,

          coldata2 varchar(50) ------------"

    How can we define the Root(eg feed1) and first level child(eg Table) node from the dynamically loaded file as specified in this statement

    Eg: (@Handle,'/feed1/Table/column',2)

    Similarly, how can we specify other attributes requiring the XML Nodes dynamically

    Appreciate your further help on this.

    Regards,

    Satish

  • OPENXML really can't do what you want it to do. It wants name-value pairs, and your XML doesn't provide them. Sure, it has names, and it has values, but they're not organized into tuples.

    The right solution here is to build a component outside the database that interoperates with the file system to find the XML file and uses XSLT to transform it into a structure usable by OPENXML. You'll find that to be much more maintainable in the long run, as well.

    Robert Rossney

    rbr@well.com

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply