Home Forums Programming XML Problem Extracting Data from XML File into Field in SQL Server Table RE: Problem Extracting Data from XML File into Field in SQL Server Table

  • You can much more easily get the data out of your XML by using XQuery on your table. So the below would be used after you insert your data into your XMLImport table:
    WITH XMLNAMESPACES(DEFAULT 'https://adview.online/XMLSchema') --Declare the Namespace
    SELECT c.j.value('(title/text())[1]','varchar(50)') AS Title, --Returns the value of the first 'title' node
           c.j.value('(company/text())[1]','varchar(50)') AS Company --Returns the value of the first 'company' node
    FROM XMLImport X
      CROSS APPLY X.XMLColumn.nodes('/jobs/job') c(j); --Cross apply to the jobs/job node, so that two rows (with the sample data) are created

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk