Query an XML file

I need to query an XML file. I thought about loading it to a dataset. But then I am not able to apply SQL on it? How can I query the dataset that comes from an XML file stored on disk?
Frans_TruyensAsked:
Who is Participating?
 
sognoctConnect With a Mentor Commented:
once loaded the xml into dataset in framework 4.0 you can use LINQ for doing queries :

example here :
http://msdn.microsoft.com/en-us/library/bb386910.aspx

in framework 2.0 instead can use datatable.select method
example here :
http://msdn.microsoft.com/en-us/library/det4aw50.aspx

or in framework 2.0 can use dataview rowfilter (really fast) for filtering data inside a datatable
0
 
Rahul AgarwalTeam LeaderCommented:
Try This Code:
  CREATE TABLE XmlImportTest
    (
        xmlFileName VARCHAR(300),
        xml_data xml
    )
    GO

    DECLARE @xmlFileName VARCHAR(300)
    SELECT  @xmlFileName = 'C:\xmlPath.xml'
    -- dynamic sql is just so we can use @xmlFileName variable in OPENROWSET
    EXEC('
    INSERT INTO XmlImportTest(xmlFileName, xml_data)

    SELECT ''' + @xmlFileName + ''', xmlData
    FROM
    (
        SELECT  *
        FROM    OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA
    ) AS FileImport (XMLDATA)
    ')
    GO
    SELECT * FROM XmlImportTest

    DROP TABLE XmlImportTest

Reference:
http://stackoverflow.com/questions/11631352/import-xml-file-into-sql-server-table
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.