Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Hw to load same Metadata Multiple Xml Files Into Table using ssis

Answered Hw to load same Metadata Multiple Xml Files Into Table using ssis

  • Thursday, February 14, 2013 6:33 AM
     
     
    Sign In to Vote
    0

    Hi Friends,

    My Requirement is To load The data from 700 xml files into tables using ssis all xml files contains same metadata.

    I used xml source to load the data but when ever the source file is changed its validating target &give the error at that movement i need to delete invalid columns &then run so this is not good way for huge tables...........................

    Please anybody help from this tag this is emergency requirement for me plzzzzzzzzzzz

     

All Replies

  • Thursday, February 14, 2013 12:32 PM
     
     Answered Has Code
    Sign In to Vote
    0

    Hi,

      Load 700 xml files into same table ?

      If you want to validate XML files before inserting into table,create XML SCHEMA COLLECTION,even this will make your package fail ,if your XML doesn't pass the schema validation.

      If each of your XML file structure is going to change,you have to manually write code for each XML file using XQUERY & XPATH like below example:

    --Inside stored procedure, use OPENROWSET to load XML into temp table. Then using Xquery shred like below example.
--You can also do this using SSIS,create the stored procedure in SSMS and then call the stored procedure using EXECUTE SQL TASK
CREATE PROCEDURE LoadXML
AS
----sample XML
--'<?xml version="1.0" encoding="UTF-8"?>
--<testrun name="Addevent (1)" project="Cal_AddEvent" tests="1" started="1" failures="0" errors="0" ignored="0">
-- <testsuite name="com.example.tests.Addevent" time="58.189">
--    <testcase name="testTC09CalendarAddEvent" classname="com.example.tests.Addevent" time="58.189"/>
--  </testsuite>
--</testrun>'
BEGIN

DECLARE @tmp table(X XML )

INSERT INTO @tmp(X)
SELECT * FROM OPENROWSET(
   BULK 'c:\SampleFolder\SampleData3.xml',
   SINGLE_BLOB) AS x;

INSERT INTO Actual_tbl  
SELECT 
	a.i.value('../@name', 'varchar(30)') name,
    a.i.value('../@project', 'varchar(30)') project,
	a.i.value('../@tests', 'varchar(30)') tests,
	a.i.value('../@started', 'varchar(30)') started,
	a.i.value('../@failures', 'varchar(30)') failures,
	a.i.value('../@errors', 'varchar(30)') errors,
	a.i.value('../@ignored', 'varchar(30)' ) ignored,
	a.i.value('@name', 'varchar(30)' ) testsuitename,
	a.i.value('@time', 'varchar(30)' ) time,
	b.j.value('@name', 'varchar(30)' ) testcasename,
	b.j.value('@classname', 'varchar(30)' ) classname,
	b.j.value('@time', 'varchar(30)' ) testcasetime
FROM @tmp
    CROSS APPLY X.nodes('/testrun/testsuite') as a(i)
	OUTER APPLY i.nodes('testcase') as b(j)
	
END

    Thanks & Regards, sathya