SQL Server Performance

extract xml data from a variable xml path

Discussion in 'SQL Server 2005 Integration Services' started by Sheetal, Oct 15, 2007.

  1. Sheetal New Member

    Hi Sir,
    I am trying to get xml data from xml file into MS SQL DB.
    For this I use following code in SP.
    SET @XDoc = ( SELECT * FROM OPENROWSET
    (BULK @VCnfVal, SINGLE_CLOB ) AS xmlData)
    Please note that I want @VCnfVal as one variable and not hard-coded.
    Below code works fine but code 1 fails. Please let me know how to use the xml variable path instead of one fixed value of the xml path.
    SET @XDoc = ( SELECT * FROM OPENROWSET
    (BULK 'C:Documents and SettingssheetalDesktop', SINGLE_CLOB ) AS xmlData)
    Sheetal, Oct 15, 2007
    #2

  2. satya Moderator

    satya, Oct 15, 2007
    #3

  3. dineshasanka Moderator

    Rather than using sps, youcan use XML File as a data source. There is a control avaialbe for this.
    dineshasanka, Oct 17, 2007
    #4

  4. Adriaan New Member

    The original script probably doesn't work because functions like OPENROWSET do not accept variables as parameters, only fixed strings. The work-around for that is to do these calls as dynamic SQL.
    But this is the 2005 Integration Services forum, so stick to other people's advice.
    Adriaan, Oct 17, 2007
    #5

  5. satya Moderator

    SSIS, all types of data (structured, unstructured, XML, etc.) are converted to a tabular (columns and rows) structure before being loaded into its buffers. Any data operation that can be applied to tabular data can be applied to the data at any step in the data-flow pipeline. This means that a single data-flow pipeline can integrate diverse sources of data and perform arbitrarily complex operations on these data without having to stage the data.
    satya, Oct 17, 2007
    #6

Share This Page