June 12, 2012 at 9:27 am
Anyone have a good resource for how to import XML into SQL Server? The SSIS XML source tool isn't clear on how to insert data into a table once it is through that tool. I've tried using OPENXML in T-SQL, but I can't figure out how to reference the file I want to import. I don't simply want to import the data into an XML typed column, I want each tag to go to a separate column. Is bringing the data into an XML typed column an interim step? If so, how does one then parse it out?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
June 12, 2012 at 10:19 am
An excellent set of articles on all kinds of XML processing. Well worth the time to read:
http://www.sqlservercentral.com/articles/SS2K5+-+XML/3022/
June 12, 2012 at 11:49 am
Stefan Krzywicki (6/12/2012)
Anyone have a good resource for how to import XML into SQL Server? The SSIS XML source tool isn't clear on how to insert data into a table once it is through that tool. I've tried using OPENXML in T-SQL, but I can't figure out how to reference the file I want to import. I don't simply want to import the data into an XML typed column, I want each tag to go to a separate column. Is bringing the data into an XML typed column an interim step? If so, how does one then parse it out?
Once you've added in the XML source with the schema attached, if you were to drag the green output to a SSIS destination it will prompt you to pick what type of node to insert into it.
So if you were to have an input file with :
<root>
<name> <first>Bob</first><last>Smith</last></name>
<name> <first>John</first><last>Smith</last></name>
<name> <first>Art</first><last>Garfunkel</last></name>
<name> <first>Edna</first><last>Smith</last></name>
<someOtherNode>....etc...</someOtherNode>
</root>
Then you could drag the green line to an output, pick the "name" level, and have the option to insert first and last into specific columns in a table.
As you can tell - this may not always work for every kind of structure, so you would need to deal with each level separately and design how to join them together. You may need to do some transforming to get it to the layout you want.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 12, 2012 at 11:57 am
Matt Miller (#4) (6/12/2012)
Stefan Krzywicki (6/12/2012)
Anyone have a good resource for how to import XML into SQL Server? The SSIS XML source tool isn't clear on how to insert data into a table once it is through that tool. I've tried using OPENXML in T-SQL, but I can't figure out how to reference the file I want to import. I don't simply want to import the data into an XML typed column, I want each tag to go to a separate column. Is bringing the data into an XML typed column an interim step? If so, how does one then parse it out?Once you've added in the XML source with the schema attached, if you were to drag the green output to a SSIS destination it will prompt you to pick what type of node to insert into it.
So if you were to have an input file with :
<root>
<name> <first>Bob</first><last>Smith</last></name>
<name> <first>John</first><last>Smith</last></name>
<name> <first>Art</first><last>Garfunkel</last></name>
<name> <first>Edna</first><last>Smith</last></name>
<someOtherNode>....etc...</someOtherNode>
</root>
Then you could drag the green line to an output, pick the "name" level, and have the option to insert first and last into specific columns in a table.
As you can tell - this may not always work for every kind of structure, so you would need to deal with each level separately and design how to join them together. You may need to do some transforming to get it to the layout you want.
Well, here's the problem, my structure is
<FTHeader> (that's the root)
<Transaction>
<TID>
<Name>
<Cost>
... 20 more tags
<BusDate>
</Transaction>
</FTHeader>[/Code]
as generated by the SQL Server FOR XML Path command
FOR XML Path('Transaction'), Root('FTHeader'), type) data
When I use the XML Source tool in SSIS, the documentation implies that I should have a column of Transaction and under that will be all the other tags. Instead, I see ALL the tags as output from the tool and have no idea how to turn them back into rows for insert into a table.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
June 12, 2012 at 12:03 pm
David Webb-200187 (6/12/2012)
An excellent set of articles on all kinds of XML processing. Well worth the time to read:
Thanks, while not exactly what I'm looking for, there's some good content there. I appreciate the link.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
June 12, 2012 at 12:04 pm
Just drag the output line to your destination and pick the Transaction as your source. once you do that and go into the mapping detail in the destination, you should see the tags within transaction as "columns" you can map.
This may depend on what schema you set up as well.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 12, 2012 at 12:06 pm
Generally, I've been lazy and pulled the raw XML into a staging table with an XML datatype column, then used XQuery to shred the XML into relational format. I've neglected to educate myself properly on the XML tools in SSIS, while I've spent a lot of days teaching myself XQuery, so it's easier that way for me.
The one time I used SSIS directly to put XML into a table, it was flat XML into a flat table, and BIDS wizards figured it out on their own (essentially), when building the SSIS package. BIDS also built the XSD for me.
But with more complex XML, I just stage it and then shred it through T-SQL XQuery.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 12, 2012 at 12:07 pm
A quick example:
DECLARE @XML XML = '<FTHeader>
<Transaction>
<TID>12345</TID>
<Name>John Smith</Name>
<Cost>24.95</Cost>
<BusDate>04/12/2012</BusDate>
</Transaction>
</FTHeader>' ;
-- select @xml
SELECT S.value('(TID)[1]', 'int') AS ApplicationID,
S.value('(Name)[1]', 'varchar(50)') AS StartTime,
S.value('(Cost)[1]', 'numeric(12,2)') AS StartDate,
S.value('(BusDate)[1]', 'date') AS Direction
FROM @XML.nodes('/FTHeader/Transaction') AS Ssn (s);
June 12, 2012 at 12:09 pm
Matt Miller (#4) (6/12/2012)
Just drag the output line to your destination and pick the Transaction as your source. once you do that and go into the mapping detail in the destination, you should see the tags within transaction as "columns" you can map.This may depend on what schema you set up as well.
Sadly, when I do that, I get Transaction as my only column. All the examples show that I should then see all the sub tags that are under Transaction in the file, but I only see "transaction_id" which I believe is the generated id for the Transaction tag.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
June 12, 2012 at 12:11 pm
GSquared (6/12/2012)
Generally, I've been lazy and pulled the raw XML into a staging table with an XML datatype column, then used XQuery to shred the XML into relational format. I've neglected to educate myself properly on the XML tools in SSIS, while I've spent a lot of days teaching myself XQuery, so it's easier that way for me.The one time I used SSIS directly to put XML into a table, it was flat XML into a flat table, and BIDS wizards figured it out on their own (essentially), when building the SSIS package. BIDS also built the XSD for me.
But with more complex XML, I just stage it and then shred it through T-SQL XQuery.
Yeah, I'm currently planning to bulk load into an xml column and then use this method[/url] to get the data out.
I keep seeing references to OPENXML, but that might also require data to already be in an XML column as I never see any information about referencing a file.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
June 12, 2012 at 12:14 pm
David Webb-200187 (6/12/2012)
A quick example:
DECLARE @XML XML = '<FTHeader>
<Transaction>
<TID>12345</TID>
<Name>John Smith</Name>
<Cost>24.95</Cost>
<BusDate>04/12/2012</BusDate>
</Transaction>
</FTHeader>' ;
-- select @xml
SELECT S.value('(TID)[1]', 'int') AS ApplicationID,
S.value('(Name)[1]', 'varchar(50)') AS StartTime,
S.value('(Cost)[1]', 'numeric(12,2)') AS StartDate,
S.value('(BusDate)[1]', 'date') AS Direction
FROM @XML.nodes('/FTHeader/Transaction') AS Ssn (s);
Thanks for the example. My main problem is that I see many instances of this kind of example, but nothing about getting the data into the XML variable in the first place. How do you populate @XML (or whatever variable name you want to use) with the XML data from a file?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
June 12, 2012 at 12:19 pm
A quick example:
declare @x xml
select @x = BulkColumn from openrowset (
Bulk 'C:\Documents and Settings\drwebb\My Documents\cost center compare\TBCostCenterHierarchy.xml',Single_blob) as x
June 12, 2012 at 12:29 pm
David Webb-200187 (6/12/2012)
A quick example:
declare @x xml
select @x = BulkColumn from openrowset (
Bulk 'C:\Documents and Settings\drwebb\My Documents\cost center compare\TBCostCenterHierarchy.xml',Single_blob) as x
Thanks, I appreciate the assistance.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
June 12, 2012 at 1:09 pm
Stefan Krzywicki (6/12/2012)
Matt Miller (#4) (6/12/2012)
Just drag the output line to your destination and pick the Transaction as your source. once you do that and go into the mapping detail in the destination, you should see the tags within transaction as "columns" you can map.This may depend on what schema you set up as well.
Sadly, when I do that, I get Transaction as my only column. All the examples show that I should then see all the sub tags that are under Transaction in the file, but I only see "transaction_id" which I believe is the generated id for the Transaction tag.
It sounds like there's something in the schema or something not being conveyed about the XML files themselves (e.g. if those sub-nodes are defined as "ref" to another element rather than inline, you may run into some issues). If you haven't tried this before, try removing any schema reference and let SSIS create its own based solely on the XML sample file.
Otherwise you could do XSLT-type transforms to get rid of whatever is preventing it.
There are options on how to tackle it. Unfortunately they would probably need to involve an actual XML file and XSD to see what is trippping SSIS up.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 12, 2012 at 1:13 pm
Matt Miller (#4) (6/12/2012)
Stefan Krzywicki (6/12/2012)
Matt Miller (#4) (6/12/2012)
Just drag the output line to your destination and pick the Transaction as your source. once you do that and go into the mapping detail in the destination, you should see the tags within transaction as "columns" you can map.This may depend on what schema you set up as well.
Sadly, when I do that, I get Transaction as my only column. All the examples show that I should then see all the sub tags that are under Transaction in the file, but I only see "transaction_id" which I believe is the generated id for the Transaction tag.
It sounds like there's something in the schema or something not being conveyed about the XML files themselves (e.g. if those sub-nodes are defined as "ref" to another element rather than inline, you may run into some issues). If you haven't tried this before, try removing any schema reference and let SSIS create its own based solely on the XML sample file.
Otherwise you could do XSLT-type transforms to get rid of whatever is preventing it.
There are options on how to tackle it. Unfortunately they would probably need to involve an actual XML file and XSD to see what is trippping SSIS up.
I'd let the XML Source tool create the XSD and tried again when I first ran into this problem.
I'll check some of the other files and see if their structure is also causing problems.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply