SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Shred XML column using SSIS


Shred XML column using SSIS

Author
Message
arun1_m1
arun1_m1
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1051 Visits: 612
Comments posted to this topic are about the item Shred XML column using SSIS
Samuel Vella
Samuel Vella
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1955 Visits: 2144
Hi Arun

Nice walk through on how to use SSIS for shredding XML sources, I usually prefer to use SQL (Example below) but as it's not always possible to use ones preferred solution its good to have alternatives

CREATE TABLE [dbo].[TestXML](
[id] [int] IDENTITY(1,1) NOT NULL,
[computed_column] [xml] NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[TestXML]
([computed_column])
VALUES
(' <x:books xmlns:x="urn:books">
<book id="743567">
<author>Arun Mishra</author>
<title>The First Article to SSC</title>
<genre>Fiction</genre>
<price>100.00</price>
<pub_date>2013-01-28</pub_date>
<review>Interesting book</review>
</book>
</x:books>')

SELECT S.ID as TableID,
ID = book.value('(@id)','int'),
author = book.value('(author)[1]', 'varchar(100)'),
title = book.value('(title)[1]', 'varchar(100)'),
genre = book.value('(genre)[1]', 'varchar(100)'),
price = book.value('(price)[1]', 'money'),
pub_date = book.value('(pub_date)[1]', 'datetime'),
review = book.value('(review)[1]', 'varchar(1000)')
FROM
TestXML S
CROSS APPLY computed_column.nodes('/*:books/book') AS Tbl(book)


INSERT INTO [dbo].[TestXML]
([computed_column])
VALUES
(' <x:books xmlns:x="urn:books">
<book id="743568">
<author>Sam Vella</author>
<title>A reply to the article</title>
<genre>Specialist</genre>
<price>0.99</price>
<pub_date>2013-01-28</pub_date>
<review>A good lunch hour read</review>
</book>
<book id="743569">
<author>Philip K Dick</author>
<title>Do Androids Dream of Electric Sheep</title>
<genre>Fiction</genre>
<price>5.99</price>
<pub_date>1968-01-01</pub_date>
<review>An even better read</review>
</book>
</x:books>')

SELECT S.ID as TableID,
ID = book.value('(@id)','int'),
author = book.value('(author)[1]', 'varchar(100)'),
title = book.value('(title)[1]', 'varchar(100)'),
genre = book.value('(genre)[1]', 'varchar(100)'),
price = book.value('(price)[1]', 'money'),
pub_date = book.value('(pub_date)[1]', 'datetime'),
review = book.value('(review)[1]', 'varchar(1000)')
FROM
TestXML S
CROSS APPLY computed_column.nodes('/*:books/book') AS Tbl(book)


phleduc
phleduc
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 74
I am not able to get this to work, setting XML data from variable in XML source editor, variable name vXMLdata, either using "inline schema" or try to generate XSD, getting error "data at root level is invalid, line 1, position 1, is it possible to make a complete package available so I can try to figure it out?
JAMESC2003
JAMESC2003
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 69
I would also like to download the package as I am a newbie
in the SSIS world. Thanks, James C
fregatepallada
fregatepallada
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 60
I would personally use XQuery - a first class citizen in T-SQL
IMHO loading XML documents is quite memory-hungry.

Cheers
JAMESC2003
JAMESC2003
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 69
What would the XQuery option buy you as far as process a series of
flat files ?
fregatepallada
fregatepallada
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 60
JAMESC2003 (2/27/2013)
What would the XQuery option buy you as far as process a series of
flat files ?
AFTER you pass XML into Stored Procedure. Plus XML native data type in SQL server allowed you to have a STRONGLY TYPED XML parameter, bound by XML schema.
itis4junk
itis4junk
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 69
I'm using SSDT 2010 that "Data Conversion" task in Data Flow keeps changing all input columns to "Unicode text stream [DT_NTEXT]" datatype; therefore, the package fails to populate data in destination table.

Thanks,


Khanh
SathyanarrayananS
SathyanarrayananS
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 26
Hi ,

Can you explain ,more on what you have done inside DataFlow task.
japa62
japa62
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 51
If none of the above benefits of XQuery were involved (and doing the best NOT to log on to work for an investigation), what are the performance differences between the SSIS method and XQuery?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search