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


Insert XML into SQL Table


Insert XML into SQL Table

Author
Message
sasken
sasken
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3634 Visits: 1948
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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63949 Visits: 17974
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10484 Visits: 5157
Hi,

It is like the other inserts.

Example
The table:
CREATE TABLE [dbo].[UrlSet](
[Tip] [smallint] NOT NULL,
[url] [xml] NULL
)

The insert:
Insert Into UrlSet(Tip,url)
Values (1,'<Urls NavigationGroup="1">
<Item ItemUrl="~/About.aspx" />
</Urls>')

Igor Micev,
My blog: www.igormicev.com
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63949 Visits: 17974
IgorMi (6/5/2012)
Hi,

It is like the other inserts.

Example
The table:
CREATE TABLE [dbo].[UrlSet](
[Tip] [smallint] NOT NULL,
[url] [xml] 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,
[url] [xml] 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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
GSquared
GSquared
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59023 Visits: 9730
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
Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10484 Visits: 5157
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,
[url] [xml] 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,
[url] [xml] 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
Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10484 Visits: 5157
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
sasken
sasken
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3634 Visits: 1948
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
GSquared
GSquared
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59023 Visits: 9730
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
sasken
sasken
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3634 Visits: 1948
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
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