Post reply

Reading data from an XML File

  • September 17, 2003 at 2:47 am

    #82950

    I am having a XML file. I want to read the data from that XML file and insert into a new table.

  • September 17, 2003 at 3:20 am

    #474579

    Try using using OPENXML.

    There is a good example in BOL for the same.

    Pay Respect to People on your way up. For you will meet the same People on your way down.


    He who knows others is learned but the wise one is one who knows himself.

  • September 17, 2003 at 3:31 am

    #474583

    Actually when i am using OPENXML concept, i am storing the whole XML Coding part in a variable, and then i can use the OPENXML concept by which i can accomplish the task. But i donot want like that. The content of the xml is contained in an XML file with an extension of .xml file. I want to read the content directly by passing the path of the XML file. Please suggest something for the query by which i can retrive the content of the XML file and inserts into the Table.

    Thanks,

    Ratikanta.

    quote:

    Try using using OPENXML.

    There is a good example in BOL for the same.

    Pay Respect to People on your way up. For you will meet the same People on your way down.

  • September 17, 2003 at 4:40 am

    #474596

    Looked at SQLXML functionality? bulk upload etc.. I may be missing the point here ..

    Chris Kempster

    http://www.chriskempster.com

    Author of "SQL Server 2k for the Oracle DBA"


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • September 19, 2003 at 1:09 am

    #474983

    You can use SQLXMLBulkLoad (SQLXML3.0 SP1) in this way

    first you have to create table

    Customer (CustomerId int,CompanyName varchar,City varchar)

    Insertcustomers.vbs -- vb script

    Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad.3.0")

    objBL.ConnectionString = "provider=SQLOLEDB.1;data source=MyServer;database=MyDatabase;uid=sa;pwd=pass"

    objBL.ErrorLogFile = "c:\error.log"

    objBL.Execute "c:\customermapping.xml", "c:\customers.xml"

    Set objBL = Nothing

    customers.xml -- file you want to import

    <ROOT>

    <Customers>

    <CustomerId>1111</CustomerId>

    <CompanyName>Sean Chai</CompanyName>

    <City>NY</City>

    </Customers>

    <Customers>

    <CustomerId>1112</CustomerId>

    <CompanyName>Tom Johnston</CompanyName>

    <City>LA</City>

    </Customers>

    <Customers>

    <CustomerId>1113</CustomerId>

    <CompanyName>Institute of Art</CompanyName>

    </Customers>

    </ROOT>

    Customermapping.xml -- mapping xml elements to table and table fields

    <?xml version="1.0" ?>

    <Schema xmlns="urn:schemas-microsoft-com:xml-data"

    xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"

    xmlns:sql="urn:schemas-microsoft-com:xml-sql" >

    <ElementType name="CustomerId" dt:type="int" />

    <ElementType name="CompanyName" dt:type="string" />

    <ElementType name="City" dt:type="string" />

    <ElementType name="ROOT" sql:is-constant="1">

    <element type="Customers" />

    </ElementType>

    <ElementType name="Customers" sql:relation="Customer">

    <element type="CustomerId" sql:field="CustomerId" />

    <element type="CompanyName" sql:field="CompanyName" />

    <element type="City" sql:field="City" />

    </ElementType>

    </Schema>

    If you want import xml file directly from sp you can use

    sp_OA**** procedures

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

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