Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Insert XML into SQL Table Expand / Collapse
Author
Message
Posted Tuesday, June 5, 2012 1:29 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:23 PM
Points: 1,360, Visits: 1,761
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
Post #1311448
Posted Tuesday, June 5, 2012 2:08 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's 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)
Post #1311461
Posted Tuesday, June 5, 2012 2:10 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 2:51 AM
Points: 3,076, Visits: 3,194
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,
SQL Server developer at Seavus
www.seavus.com
Post #1311462
Posted Tuesday, June 5, 2012 2:16 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's 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)
Post #1311466
Posted Tuesday, June 5, 2012 2:28 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
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
Post #1311470
Posted Tuesday, June 5, 2012 2:28 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 2:51 AM
Points: 3,076, Visits: 3,194
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,
SQL Server developer at Seavus
www.seavus.com
Post #1311471
Posted Tuesday, June 5, 2012 2:37 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 2:51 AM
Points: 3,076, Visits: 3,194
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,
SQL Server developer at Seavus
www.seavus.com
Post #1311475
Posted Tuesday, June 5, 2012 3:05 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:23 PM
Points: 1,360, Visits: 1,761
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
Post #1311489
Posted Wednesday, June 6, 2012 9:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
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
Post #1311948
Posted Thursday, June 7, 2012 9:16 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:23 PM
Points: 1,360, Visits: 1,761
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
Post #1312587
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse