Post reply

Querying XML File

  • December 3, 2007 at 9:05 am

    #201379

    I am very confused! I have an XML file (which is actually an SSIS configuration file) that contains one key/value pair that I need to access using TSQL. Looking at the examples on the net it appears that I have to include the contents of the file, albeit assigned to a variable, and then create an internal representation of the file. Once I have done that only then can I query the data.

    Have I misunderstood what is required or is there an easier way of retrieving the value that I need i.e just one key/value pair?

  • December 3, 2007 at 9:27 am

    #756532

    Not sure about the internal representation you mean, but basically yes, you need to load the whole document to process it. In order to get a single value you are looking for you could use a statement like:

    DECLARE @xml XML

    SELECT @xml = CAST(BulkColumn AS XML)

    FROM OPENROWSET(BULK 'c:\somefile.xml', SINGLE_BLOB) AS x

    SELECT @xml.query('//pathtomysetting@attribname')

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • December 3, 2007 at 11:48 am

    #756605

    Hi David,

    Check the following links

    http://www.simple-talk.com/sql/sql-server-2005/beginning-sql-server-2005-xml-programming/[/url].

    Reading an XML file from SQL Server

    http://www.builderau.com.au/architect/webservices/soa/Reading-an-XML-file-from-SQL-Server/0,339024590,339174685,00.htm

    How to import XML into SQL Server with the XML Bulk Load component

    http://support.microsoft.com/kb/316005

    Regards,

    Ahmed

  • December 4, 2007 at 5:54 am

    #756888

    Hi,

    Thanks to both of you for the information, it has given me a better idea of how I can achieve what I am trying to do.

    Regards

    David

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

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