June 5, 2012 at 1:29 pm
Hi Everyone,
Hope all is well.
I need to insert the below xml into a SQL Server 2008 R2 Database table.
<?xml version="1.0" encoding="UTF-8"?>
<sessions accountId='48152' startDate='2012-05-27' startTime='00:00:00' endDate='2012-05-28' endTime='00:00:00'>
<session applicationId='357627' applicationName='app1' startTime='00:48:54' startDate='2012-05-27' type='abc' direction='outbound' durationMinutes='0.43' token='113ddd5999257543963a760789fc12344f27cb0bcc519f0ce6d3a907fccb0d56361c4e3151c3780c35c752cb' bridged='false' callerId='1234567890' sessionId='86c368af24d7eab2a84ec6660704f3d4' connectionId='0'>
<outboundDialString dialString='11234567890'/>
</session>
<session applicationId='112549' applicationName='app2' startTime='01:14:33' startDate='2012-05-27' type='abc' direction='inbound' durationMinutes='2.73' calledId='1234567890' bridged='false' callerId='1234567890' sessionId='7d35636be123418f93fe638a9bac78d9' connectionId='0'/>
<session applicationId='357627' applicationName='app3' startTime='00:48:54' startDate='2012-05-27' type='abc' direction='outbound' durationMinutes='0.43' token='113ddd5999257543963a760789fc12344f27cb0bcc519f0ce6d3a907fccb0d56361c4e3151c3780c35c752cb' bridged='false' callerId='1234567890' sessionId='86c368af24d7eab2a84ec6660704f3d4' connectionId='0'>
<outboundDialString dialString='11234567890'/>
</session>
<session applicationId='357627' applicationName='app4' startTime='00:48:54' startDate='2012-05-27' type='abc' direction='outbound' durationMinutes='0.43' token='113ddd5999257543963a760789fc12344f27cb0bcc519f0ce6d3a907fccb0d56361c4e3151c3780c35c752cb' bridged='false' callerId='1234567890' sessionId='86c368af24d7eab2a84ec6660704f3d4' connectionId='0'>
<outboundDialString dialString='11234567890'/>
</session>
<session applicationId='357627' applicationName='app5' startTime='00:48:54' startDate='2012-05-27' type='abc' direction='outbound' durationMinutes='0.43' token='113ddd5999257543963a760789fc12344f27cb0bcc519f0ce6d3a907fccb0d56361c4e3151c3780c35c752cb' bridged='false' callerId='1234567890' sessionId='86c368af24d7eab2a84ec6660704f3d4' connectionId='0'>
<outboundDialString dialString='11234567890'/>
</session>
<session applicationId='357627' applicationName='app6' startTime='00:48:54' startDate='2012-05-27' type='abc' direction='outbound' durationMinutes='0.43' token='113ddd5999257543963a760789fc12344f27cb0bcc519f0ce6d3a907fccb0d56361c4e3151c3780c35c752cb' bridged='false' callerId='1234567890' sessionId='86c368af24d7eab2a84ec6660704f3d4' connectionId='0'>
<outboundDialString dialString='11234567890'/>
</session>
</sessions>
I have not done this before. I want to know how to identify the columns in above xml and insert them into the table accordingly. Thanks in advance for your valuable inputs.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
June 5, 2012 at 2:08 pm
With over 400 points you know what is coming...
We need some details here. You want to insert what into what table? What does the table look like?
DDL, sample data (maybe this is the sample data), desired output. See the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 5, 2012 at 2:10 pm
June 5, 2012 at 2:16 pm
IgorMi (6/5/2012)
Hi,It is like the other inserts.
Example
The table:
CREATE TABLE [dbo].[UrlSet](
[Tip] [smallint] NOT NULL,
)
The insert:
Insert Into UrlSet(Tip,url)
Values (1,'<Urls NavigationGroup="1">
<Item ItemUrl="~/About.aspx" />
</Urls>')
I don't know what your other inserts look like.
Does this do what you want?
declare @xml xml = '<?xml version="1.0" encoding="UTF-8"?>
<sessions accountId=''48152'' startDate=''2012-05-27'' startTime=''00:00:00'' endDate=''2012-05-28'' endTime=''00:00:00''>
<session applicationId=''357627'' applicationName=''app1'' startTime=''00:48:54'' startDate=''2012-05-27'' type=''abc'' direction=''outbound'' durationMinutes=''0.43'' token=''113ddd5999257543963a760789fc12344f27cb0bcc519f0ce6d3a907fccb0d56361c4e3151c3780c35c752cb'' bridged=''false'' callerId=''1234567890'' sessionId=''86c368af24d7eab2a84ec6660704f3d4'' connectionId=''0''>
<outboundDialString dialString=''11234567890''/>
</session>
<session applicationId=''112549'' applicationName=''app2'' startTime=''01:14:33'' startDate=''2012-05-27'' type=''abc'' direction=''inbound'' durationMinutes=''2.73'' calledId=''1234567890'' bridged=''false'' callerId=''1234567890'' sessionId=''7d35636be123418f93fe638a9bac78d9'' connectionId=''0''/>
<session applicationId=''357627'' applicationName=''app3'' startTime=''00:48:54'' startDate=''2012-05-27'' type=''abc'' direction=''outbound'' durationMinutes=''0.43'' token=''113ddd5999257543963a760789fc12344f27cb0bcc519f0ce6d3a907fccb0d56361c4e3151c3780c35c752cb'' bridged=''false'' callerId=''1234567890'' sessionId=''86c368af24d7eab2a84ec6660704f3d4'' connectionId=''0''>
<outboundDialString dialString=''11234567890''/>
</session>
<session applicationId=''357627'' applicationName=''app4'' startTime=''00:48:54'' startDate=''2012-05-27'' type=''abc'' direction=''outbound'' durationMinutes=''0.43'' token=''113ddd5999257543963a760789fc12344f27cb0bcc519f0ce6d3a907fccb0d56361c4e3151c3780c35c752cb'' bridged=''false'' callerId=''1234567890'' sessionId=''86c368af24d7eab2a84ec6660704f3d4'' connectionId=''0''>
<outboundDialString dialString=''11234567890''/>
</session>
<session applicationId=''357627'' applicationName=''app5'' startTime=''00:48:54'' startDate=''2012-05-27'' type=''abc'' direction=''outbound'' durationMinutes=''0.43'' token=''113ddd5999257543963a760789fc12344f27cb0bcc519f0ce6d3a907fccb0d56361c4e3151c3780c35c752cb'' bridged=''false'' callerId=''1234567890'' sessionId=''86c368af24d7eab2a84ec6660704f3d4'' connectionId=''0''>
<outboundDialString dialString=''11234567890''/>
</session>
<session applicationId=''357627'' applicationName=''app6'' startTime=''00:48:54'' startDate=''2012-05-27'' type=''abc'' direction=''outbound'' durationMinutes=''0.43'' token=''113ddd5999257543963a760789fc12344f27cb0bcc519f0ce6d3a907fccb0d56361c4e3151c3780c35c752cb'' bridged=''false'' callerId=''1234567890'' sessionId=''86c368af24d7eab2a84ec6660704f3d4'' connectionId=''0''>
<outboundDialString dialString=''11234567890''/>
</session>
</sessions>'
CREATE TABLE #UrlSet(
[Tip] [smallint] NOT NULL,
)
insert #UrlSet select 1, @xml
select * from #UrlSet
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 5, 2012 at 2:28 pm
I think this will get you started:
DECLARE @XML XML = '<?xml version="1.0" encoding="UTF-8"?>
<sessions accountId=''48152'' startDate=''2012-05-27'' startTime=''00:00:00'' endDate=''2012-05-28'' endTime=''00:00:00''>
<session applicationId=''357627'' applicationName=''app1'' startTime=''00:48:54'' startDate=''2012-05-27'' type=''abc'' direction=''outbound'' durationMinutes=''0.43'' token=''113ddd5999257543963a760789fc12344f27cb0bcc519f0ce6d3a907fccb0d56361c4e3151c3780c35c752cb'' bridged=''false'' callerId=''1234567890'' sessionId=''86c368af24d7eab2a84ec6660704f3d4'' connectionId=''0''>
<outboundDialString dialString=''11234567890''/>
</session>
<session applicationId=''112549'' applicationName=''app2'' startTime=''01:14:33'' startDate=''2012-05-27'' type=''abc'' direction=''inbound'' durationMinutes=''2.73'' calledId=''1234567890'' bridged=''false'' callerId=''1234567890'' sessionId=''7d35636be123418f93fe638a9bac78d9'' connectionId=''0''/>
<session applicationId=''357627'' applicationName=''app3'' startTime=''00:48:54'' startDate=''2012-05-27'' type=''abc'' direction=''outbound'' durationMinutes=''0.43'' token=''113ddd5999257543963a760789fc12344f27cb0bcc519f0ce6d3a907fccb0d56361c4e3151c3780c35c752cb'' bridged=''false'' callerId=''1234567890'' sessionId=''86c368af24d7eab2a84ec6660704f3d4'' connectionId=''0''>
<outboundDialString dialString=''11234567890''/>
</session>
<session applicationId=''357627'' applicationName=''app4'' startTime=''00:48:54'' startDate=''2012-05-27'' type=''abc'' direction=''outbound'' durationMinutes=''0.43'' token=''113ddd5999257543963a760789fc12344f27cb0bcc519f0ce6d3a907fccb0d56361c4e3151c3780c35c752cb'' bridged=''false'' callerId=''1234567890'' sessionId=''86c368af24d7eab2a84ec6660704f3d4'' connectionId=''0''>
<outboundDialString dialString=''11234567890''/>
</session>
<session applicationId=''357627'' applicationName=''app5'' startTime=''00:48:54'' startDate=''2012-05-27'' type=''abc'' direction=''outbound'' durationMinutes=''0.43'' token=''113ddd5999257543963a760789fc12344f27cb0bcc519f0ce6d3a907fccb0d56361c4e3151c3780c35c752cb'' bridged=''false'' callerId=''1234567890'' sessionId=''86c368af24d7eab2a84ec6660704f3d4'' connectionId=''0''>
<outboundDialString dialString=''11234567890''/>
</session>
<session applicationId=''357627'' applicationName=''app6'' startTime=''00:48:54'' startDate=''2012-05-27'' type=''abc'' direction=''outbound'' durationMinutes=''0.43'' token=''113ddd5999257543963a760789fc12344f27cb0bcc519f0ce6d3a907fccb0d56361c4e3151c3780c35c752cb'' bridged=''false'' callerId=''1234567890'' sessionId=''86c368af24d7eab2a84ec6660704f3d4'' connectionId=''0''>
<outboundDialString dialString=''11234567890''/>
</session>
</sessions>' ;
SELECT @XML.value('(/sessions/@accountId)[1]', 'int') AS AccountID,
@XML.value('(/sessions/@startDate)[1]', 'date') AS StartDate,
@XML.value('(/sessions/@startTime)[1]', 'time') AS StartTime,
@XML.value('(/sessions/@endDate)[1]', 'date') AS EndDate,
@XML.value('(/sessions/@endTime)[1]', 'time') AS EndTime ;
SELECT Ssn.value('(/session/@applicationId)[1]', 'int') AS ApplicationID
FROM (SELECT S.s.query('.') AS Ssn
FROM @XML.nodes('/sessions/session') AS S (s)) AS SSnes ;
What I've done is turn it into a consumable XML format by assigning it to a typed variable. You can do the same with a column in table.
Then I pulled the outer data, relevant to the "sessions" level. That would go in one table (assuming normalized database design). Then the inner data can be queried, you just need to add the rest of the columns following my example. You will probably also need the AccountID in that table, which is easy enough to get into it by adding that piece from the first query to the second query, if you need it.
Does that help?
- 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 5, 2012 at 2:28 pm
Sean Lange (6/5/2012)
IgorMi (6/5/2012)
Hi,It is like the other inserts.
Example
The table:
CREATE TABLE [dbo].[UrlSet](
[Tip] [smallint] NOT NULL,
)
The insert:
Insert Into UrlSet(Tip,url)
Values (1,'<Urls NavigationGroup="1">
<Item ItemUrl="~/About.aspx" />
</Urls>')
I don't know what your other inserts look like.
Does this do what you want?
declare @xml xml = '<?xml version="1.0" encoding="UTF-8"?>
<sessions accountId=''48152'' startDate=''2012-05-27'' startTime=''00:00:00'' endDate=''2012-05-28'' endTime=''00:00:00''>
<session applicationId=''357627'' applicationName=''app1'' startTime=''00:48:54'' startDate=''2012-05-27'' type=''abc'' direction=''outbound'' durationMinutes=''0.43'' token=''113ddd5999257543963a760789fc12344f27cb0bcc519f0ce6d3a907fccb0d56361c4e3151c3780c35c752cb'' bridged=''false'' callerId=''1234567890'' sessionId=''86c368af24d7eab2a84ec6660704f3d4'' connectionId=''0''>
<outboundDialString dialString=''11234567890''/>
</session>
<session applicationId=''112549'' applicationName=''app2'' startTime=''01:14:33'' startDate=''2012-05-27'' type=''abc'' direction=''inbound'' durationMinutes=''2.73'' calledId=''1234567890'' bridged=''false'' callerId=''1234567890'' sessionId=''7d35636be123418f93fe638a9bac78d9'' connectionId=''0''/>
<session applicationId=''357627'' applicationName=''app3'' startTime=''00:48:54'' startDate=''2012-05-27'' type=''abc'' direction=''outbound'' durationMinutes=''0.43'' token=''113ddd5999257543963a760789fc12344f27cb0bcc519f0ce6d3a907fccb0d56361c4e3151c3780c35c752cb'' bridged=''false'' callerId=''1234567890'' sessionId=''86c368af24d7eab2a84ec6660704f3d4'' connectionId=''0''>
<outboundDialString dialString=''11234567890''/>
</session>
<session applicationId=''357627'' applicationName=''app4'' startTime=''00:48:54'' startDate=''2012-05-27'' type=''abc'' direction=''outbound'' durationMinutes=''0.43'' token=''113ddd5999257543963a760789fc12344f27cb0bcc519f0ce6d3a907fccb0d56361c4e3151c3780c35c752cb'' bridged=''false'' callerId=''1234567890'' sessionId=''86c368af24d7eab2a84ec6660704f3d4'' connectionId=''0''>
<outboundDialString dialString=''11234567890''/>
</session>
<session applicationId=''357627'' applicationName=''app5'' startTime=''00:48:54'' startDate=''2012-05-27'' type=''abc'' direction=''outbound'' durationMinutes=''0.43'' token=''113ddd5999257543963a760789fc12344f27cb0bcc519f0ce6d3a907fccb0d56361c4e3151c3780c35c752cb'' bridged=''false'' callerId=''1234567890'' sessionId=''86c368af24d7eab2a84ec6660704f3d4'' connectionId=''0''>
<outboundDialString dialString=''11234567890''/>
</session>
<session applicationId=''357627'' applicationName=''app6'' startTime=''00:48:54'' startDate=''2012-05-27'' type=''abc'' direction=''outbound'' durationMinutes=''0.43'' token=''113ddd5999257543963a760789fc12344f27cb0bcc519f0ce6d3a907fccb0d56361c4e3151c3780c35c752cb'' bridged=''false'' callerId=''1234567890'' sessionId=''86c368af24d7eab2a84ec6660704f3d4'' connectionId=''0''>
<outboundDialString dialString=''11234567890''/>
</session>
</sessions>'
CREATE TABLE #UrlSet(
[Tip] [smallint] NOT NULL,
)
insert #UrlSet select 1, @xml
select * from #UrlSet
Yes of course,
I just didn't want to give the primer with your quite big xml
Thanks
IgorMi
Igor Micev,My blog: www.igormicev.com
June 5, 2012 at 2:37 pm
GSquared (6/5/2012)
I think this will get you started:
DECLARE @XML XML = '<?xml version="1.0" encoding="UTF-8"?><sessions accountId=''48152'' startDate=''2012-05-27'' startTime=''00:00:00'' endDate=''2012-05-28'' endTime=''00:00:00''>
<session applicationId=''357627'' applicationName=''app1'' startTime=''00:48:54'' startDate=''2012-05-27'' type=''abc'' direction=''outbound'' durationMinutes=''0.43'' token=''113ddd5999257543963a760789fc12344f27cb0bcc519f0ce6d3a907fccb0d56361c4e3151c3780c35c752cb'' bridged=''false'' callerId=''1234567890'' sessionId=''86c368af24d7eab2a84ec6660704f3d4'' connectionId=''0''>
<outboundDialString dialString=''11234567890''/>
</session>
<session applicationId=''112549'' applicationName=''app2'' startTime=''01:14:33'' startDate=''2012-05-27'' type=''abc'' direction=''inbound'' durationMinutes=''2.73'' calledId=''1234567890'' bridged=''false'' callerId=''1234567890'' sessionId=''7d35636be123418f93fe638a9bac78d9'' connectionId=''0''/>
<session applicationId=''357627'' applicationName=''app3'' startTime=''00:48:54'' startDate=''2012-05-27'' type=''abc'' direction=''outbound'' durationMinutes=''0.43'' token=''113ddd5999257543963a760789fc12344f27cb0bcc519f0ce6d3a907fccb0d56361c4e3151c3780c35c752cb'' bridged=''false'' callerId=''1234567890'' sessionId=''86c368af24d7eab2a84ec6660704f3d4'' connectionId=''0''>
<outboundDialString dialString=''11234567890''/>
</session>
<session applicationId=''357627'' applicationName=''app4'' startTime=''00:48:54'' startDate=''2012-05-27'' type=''abc'' direction=''outbound'' durationMinutes=''0.43'' token=''113ddd5999257543963a760789fc12344f27cb0bcc519f0ce6d3a907fccb0d56361c4e3151c3780c35c752cb'' bridged=''false'' callerId=''1234567890'' sessionId=''86c368af24d7eab2a84ec6660704f3d4'' connectionId=''0''>
<outboundDialString dialString=''11234567890''/>
</session>
<session applicationId=''357627'' applicationName=''app5'' startTime=''00:48:54'' startDate=''2012-05-27'' type=''abc'' direction=''outbound'' durationMinutes=''0.43'' token=''113ddd5999257543963a760789fc12344f27cb0bcc519f0ce6d3a907fccb0d56361c4e3151c3780c35c752cb'' bridged=''false'' callerId=''1234567890'' sessionId=''86c368af24d7eab2a84ec6660704f3d4'' connectionId=''0''>
<outboundDialString dialString=''11234567890''/>
</session>
<session applicationId=''357627'' applicationName=''app6'' startTime=''00:48:54'' startDate=''2012-05-27'' type=''abc'' direction=''outbound'' durationMinutes=''0.43'' token=''113ddd5999257543963a760789fc12344f27cb0bcc519f0ce6d3a907fccb0d56361c4e3151c3780c35c752cb'' bridged=''false'' callerId=''1234567890'' sessionId=''86c368af24d7eab2a84ec6660704f3d4'' connectionId=''0''>
<outboundDialString dialString=''11234567890''/>
</session>
</sessions>' ;
SELECT @XML.value('(/sessions/@accountId)[1]', 'int') AS AccountID,
@XML.value('(/sessions/@startDate)[1]', 'date') AS StartDate,
@XML.value('(/sessions/@startTime)[1]', 'time') AS StartTime,
@XML.value('(/sessions/@endDate)[1]', 'date') AS EndDate,
@XML.value('(/sessions/@endTime)[1]', 'time') AS EndTime ;
SELECT Ssn.value('(/session/@applicationId)[1]', 'int') AS ApplicationID
FROM (SELECT S.s.query('.') AS Ssn
FROM @XML.nodes('/sessions/session') AS S (s)) AS SSnes ;
What I've done is turn it into a consumable XML format by assigning it to a typed variable. You can do the same with a column in table.
Then I pulled the outer data, relevant to the "sessions" level. That would go in one table (assuming normalized database design). Then the inner data can be queried, you just need to add the rest of the columns following my example. You will probably also need the AccountID in that table, which is easy enough to get into it by adding that piece from the first query to the second query, if you need it.
Does that help?
Hi,
I also agree with this one post.
You have now at least two options:
1) To insert the xml attributes in separate columns of a table and then use t-sqls, and
2) To insert it as a xml column and then use XQuery in your queries
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
June 5, 2012 at 3:05 pm
Thanks for your valuable inputs everyone.
I apologize for not posting my question clearly. The xml file is not always a same file instead a new file gets created with a new name every 2 minutes and I need to capture data from each file as it gets generated, although the content appears to be same as posted earlier. As of now I dont have the destination table created yet. But I will need only the below columns.
applicationId
startTime
startDate
direction
durationMinutes
token
bridged
callerId
sessionId
dialString
Once I have a query ready I can create a table accordingly and use it in a package and have it scheduled as required. Thanks for your help in advance again.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
June 6, 2012 at 9:29 am
Sapen (6/5/2012)
Thanks for your valuable inputs everyone.I apologize for not posting my question clearly. The xml file is not always a same file instead a new file gets created with a new name every 2 minutes and I need to capture data from each file as it gets generated, although the content appears to be same as posted earlier. As of now I dont have the destination table created yet. But I will need only the below columns.
applicationId
startTime
startDate
direction
durationMinutes
token
bridged
callerId
sessionId
dialString
Once I have a query ready I can create a table accordingly and use it in a package and have it scheduled as required. Thanks for your help in advance again.
All you need to do is add the columns you want to the query I wrote. Copy-and-paste and a tiny bit of typing. That should give you what you need on the XML query.
- 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 7, 2012 at 9:16 am
GSquared (6/6/2012)
Sapen (6/5/2012)
Thanks for your valuable inputs everyone.I apologize for not posting my question clearly. The xml file is not always a same file instead a new file gets created with a new name every 2 minutes and I need to capture data from each file as it gets generated, although the content appears to be same as posted earlier. As of now I dont have the destination table created yet. But I will need only the below columns.
applicationId
startTime
startDate
direction
durationMinutes
token
bridged
callerId
sessionId
dialString
Once I have a query ready I can create a table accordingly and use it in a package and have it scheduled as required. Thanks for your help in advance again.
All you need to do is add the columns you want to the query I wrote. Copy-and-paste and a tiny bit of typing. That should give you what you need on the XML query.
GSQuared: Thanks for the help. I will definitely try this. But everytime a new file gets generated I dont want to copy and paste the content from each individual file and run this script as a package or from the SSMS query window. I want to automate this process because on a single day I will have atleast a thousand of such files that get generated depending on our call volumes.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
June 7, 2012 at 9:25 am
Definitely don't copy and paste it into a script!
What you probably want to do is create an SSIS package that will use a For Each Loop to step through each file in the directory, and either directly shred the XML into tabular format, or dump the raw XML into a staging table.
If you want to do the whole thing in T-SQL instead of SSIS, then you'll need to create a dynamic OpenRowset command that will query the XML in the files.
Personally, I'd use SSIS. It's what it's for, and I find it easier to use than complex dynamic SQL. Once you set up an SSIS package, with a loop for the files, and the right Data Flow objects in the loop, the whole thing can be scheduled to run several times per day and pick up all the needed files completely automatically. It's pretty slick when you set it up right.
If you get out of your depth on that, you'll be best served by having your company hire a temporary contractor to do the SSIS work. Should be a couple of day's work, tops, for a skilled contractor.
Without actual access to your server, the files, your databases, and so on, it's going to take a long time to walk you through the whole process in an online forum. Can be done, but it'll take a while.
If you want to go that route, let us know, and try digging into Business Intelligence Dev Studio and Bing/Google/whatever to get started. Let us know when you get to questions you can't figure out on your own with searches and such. Expect that it will take a couple of weeks if it's your first SSIS project. After the initial learning curve, future projects will be faster, but the first one will be slow, frustrating, and confusing, for a while at least.
- 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 7, 2012 at 9:32 am
Thanks again Gsquared I will definitely go through the route of SSIS.
Let us know when you get to questions you can't figure out on your own with searches and such.
So kind of you. I will definitely do that as I will need help from experts like you and others in this forums.
Thanks again.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
June 7, 2012 at 9:44 am
You're welcome.
- 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 7, 2012 at 1:28 pm
Is there a way that I can loop through if I have too many xml records?
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
June 7, 2012 at 1:32 pm
Sapen (6/7/2012)
Is there a way that I can loop through if I have too many xml records?
Not sure what you mean. Too many nodes in the XML?
- 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
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply