You must Sign In to post a response.
  • Category: [Competition Entries]

    How to access XNK in stored Procedure

    Hi ,

    I have XML file and i just need to access in my stored procedure and read every node and insert into data base, any one can Help me??

    XML Format :

    <MarcDs>
    <TblISbn>
    <ISBN>9781604537000</ISBN>
    <ISBN_10>1604537000</ISBN_10>
    <Item_ />
    <UnitPrice />
    <Dewey_ />
    </TblISbn>
    <TblISbn>
    <ISBN>13255656</ISBN>
    <ISBN_10>445665</ISBN_10>
    <Item_>456654564</Item_>
    <UnitPrice>654654656</UnitPrice>
    <Dewey_>65465546</Dewey_>
    </TblISbn>
    <tblAdd>
    <ISBN />
    <LCCN />
    <OrgResponsibleMARC />
    <GeographicCode />
    </tblAdd>
    </MarcDs>


    Thanks In Advance

    Vedanayagam G
  • #702233
    One is best way you can pass xml file as ntext datatype parameter in stored proc and than you can read that xml within stored proc using sp_xml_preparedocument

    eg
    Create proc spname
    (
    @LineData nText--grid data
    )
    AS
    Begin
    DECLARE @hDoc1 int
    exec sp_xml_preparedocument @hDoc1 OUTPUT,@LineData
    if exists(select * FROM OPENXML (@hdoc1, '/NewDataSet/LineItmData',1))

    SELECT InsuranceAddressId,PrimaryNo,InsuranceId,Address1,Address2,Phone1,Phone2,Email,Fax,Fax2,City,StateID,Zip,Notes,Disabled,RecordStatus,CreatedBy,ModifiedBy

    FROM OPENXML (@hdoc1, '/NewDataSet/LineItmData',1)
    WITH (
    InsuranceAddressId int) As tblname
    -- you can insert that value in any table or what you wnat to do within sp.
    End


    Or also one more option, you can get directly xml in stored proc by using file path
    eg

    CREATE PROC [dbo].[USP_READXMLFILE]
    AS
    /*
    EXEC [USP_READXMLFILE]
    */
    BEGIN
    SET NOCOUNT ON

    DECLARE @HANDLE INT

    DECLARE @SQUERY VARCHAR(1000)
    DECLARE @XMLDOC XML

    SET @XMLDOC = (SELECT * FROM OPENROWSET
    (BULK 'd:\TestXML.xml', SINGLE_CLOB) AS XMLDATA)
    SELECT @XMLDOC


    EXEC SP_XML_PREPAREDOCUMENT @HANDLE OUTPUT,@XMLDOC

    SELECT * FROM OPENXML(@HANDLE, '/XMLData/tblEmp', 2)
    WITH (name VARCHAR(50),designation VARCHAR(50))

    END

    Regards & thanks
    Arvind kumar
    Visit--blog.akumars.esoftera.in


  • This thread is locked for new responses. Please post your comments and questions as a separate thread.
    If required, refer to the URL of this page in your new post.