July 5, 2009 at 6:27 pm
I have an XML source file and I need to place values from it into database tables. The issue is that I want to use one of the values as a value in multiple tables. How do I get access to use the field more than once? I am able to read that portion of the xml to a table, but I can not figure out how to access that field again and place it in another table with another portion of the xml file. The example below will help describe my issue. I want to be able to add a column to a Models table that uses RSID as a column. Models includes name, val, and count - and I want to add a column to the table with the RSID value.
Any help is appreciated.
July 5, 2009 at 6:50 pm
Your example did not appear, but you should check out the Multicast transformation to see whether it gives you what you need ... one source to multiple destinations.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 5, 2009 at 7:04 pm
Thanks for responding, but I tried multicasting and it does not do what I need. I think you can see the xml file now. The issue is that the field I need is in a different "group" in the xml. Can I some how set a variable to the field's value and use the variable in the derived column task?
Thanks
July 5, 2009 at 7:18 pm
Ah, yes, I see it now. That would require detailed knowledge of importing from XML, which I do not have, sorry:-) But, in SSIS, there's always a way ... it's just not always obvious. Someone else will help, I'm sure.
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 6, 2009 at 11:14 am
im not so sure what you need , if you want to get all the values in a single query this is an example :
--you may need to change the XML to your original source since this editor change it:
declare @strXML xml
set @strXML=N'
'
select Tbl.C.value('@name', 'nvarchar(50)') as [name] ,
Tbl.C.value('@val', 'nvarchar(50)') as val,
Tbl.C.value('@count', 'nvarchar(50)')as [count],
(select Tbl.C.value('@rsid', 'nvarchar(50)')as rsid
from @strXML.nodes('/solution/specifications/sessiondata') AS Tbl(C) ) as rsid
from @strXML.nodes('/solution/results/financialresults/modelstotal/product') AS Tbl(C)
July 6, 2009 at 11:41 am
PEPEPACO_1980 (7/6/2009)
im not so sure what you need , if you want to get all the values in a single query this is an example :--you may need to change the XML to your original source since this editor change it:
declare @strXML xml
set @strXML=N'
'
select Tbl.C.value('@name', 'nvarchar(50)') as [name] ,
Tbl.C.value('@val', 'nvarchar(50)') as val,
Tbl.C.value('@count', 'nvarchar(50)')as [count],
(select Tbl.C.value('@rsid', 'nvarchar(50)')as rsid
from @strXML.nodes('/solution/specifications/sessiondata') AS Tbl(C) ) as rsid
from @strXML.nodes('/solution/results/financialresults/modelstotal/product') AS Tbl(C)
Thanks for the response.
What I need to do is read the xml file (XML source) and then be able to place the RSID value in a table with the models values. So I will end up with a database table with columns RISD, name, value, count. So do I use a script task or component to place the RSID value in a variable and then use derived column to add the RSID value to the Modelstotal dataset? Then place it in a table?
I do not understand placing this code in the xml file. Can you explain? Sorry I am very new to this.
Thanks
July 6, 2009 at 11:57 am
just pass your xml as it is to a stored procedure that accepts a xml parameter like this:
CREATE procedure [dbo].[SP_GET_MODELS] (@strXML xml)
insert into MODEL
select Tbl.C.value('@name', 'nvarchar(50)') as [name] ,
Tbl.C.value('@val', 'nvarchar(50)') as val,
Tbl.C.value('@count', 'nvarchar(50)')as [count],
(select Tbl.C.value('@rsid', 'nvarchar(50)')as rsid
from @strXML.nodes('/solution/specifications/sessiondata') AS Tbl(C) ) as rsid
from @strXML.nodes('/solution/results/financialresults/modelstotal/product') AS Tbl(C)
end
leave the part of parsing the XML to the SQL Server side. hope this helps
July 6, 2009 at 12:04 pm
So this does not even use SSIS? Correct?
I need a package or procedure that will read in multiple xml files and place the data in database tables.
How would you suggest I do this? With or without SSIS?
Thanks
July 6, 2009 at 12:11 pm
I think what PEPEPACO_1980 is saying is to use a stored proceudure to parse out your XML instead of an XML data source adapter. Still use SSIS to process your files, but don't use the XML source adapter.
I think there's a way to do this with the XML adapter. I don't even see the RSID element in the XML that you've posted. Can you post an xsd file?
July 6, 2009 at 12:20 pm
if you have full control of the destination DB and it is only one, then move the SQL to a Stored procedure, if the destination tables are in multiple servers the add a "SQL Task" to you SSIS project. so you can add more destinations later.
it depends on how it will be easier for you to support in the future. it can be done in both ways.
regards.
July 6, 2009 at 12:24 pm
Here is the an xml file and xsd file.
XML
XSD File
July 6, 2009 at 12:33 pm
It looks like your xml/xsd did not show up. You may want to use the Preview option before posting.
July 6, 2009 at 12:34 pm
im had issues to post XML from iexplorer too, it worked when used chrome.
regards.
July 6, 2009 at 12:41 pm
Trying again.
XML
July 6, 2009 at 12:43 pm
XSD
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply