June 7, 2012 at 1:41 pm
I have modified the query a bit per my requirement and it is as shown:
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 Ssn.value('(/sessions/session/@applicationId)[1]', 'int') AS ApplicationID,
Ssn.value('(/sessions/session/@startTime)[1]', 'time') AS StartTime,
Ssn.value('(/sessions/session/@startDate)[1]', 'date') AS StartDate,
Ssn.value('(/sessions/session/@direction)[1]', 'varchar(max)') AS Direction,
Ssn.value('(/sessions/session/@token)[1]', 'varchar(max)') AS Token,
Ssn.value('(/sessions/session/@bridged)[1]', 'varchar(max)') AS Bridged,
Ssn.value('(/sessions/session/@callerId)[1]', 'bigint') AS CallerId,
Ssn.value('(/sessions/session/@sessionId)[1]', 'varchar(max)') AS SessionId,
Ssn.value('(/sessions/session/outboundDialString/@dialString)[1]', 'varchar(max)') AS DialString
FROM (SELECT S.s.query('.') AS Ssn
FROM @XML.nodes('/sessions/session') AS S (s)) AS SSnes ;
And my output is null for every column I select as shown in the attachment.
“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:47 pm
my query could definitely be wrong..
“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:54 pm
You need to take "/sessions" out of there.
You're already going to that level because of the nodes() piece, so you need to start at "/session". nodes() is correct, value() needs to be modified.
Like:
SELECT Ssn.value('(/session/@applicationId)[1]', 'int') AS ApplicationID,
Ssn.value('(/session/@startTime)[1]', 'time') AS StartTime,
Ssn.value('(/session/@startDate)[1]', 'date') AS StartDate,
Ssn.value('(/session/@direction)[1]', 'varchar(max)') AS Direction,
Ssn.value('(/session/@token)[1]', 'varchar(max)') AS Token,
Ssn.value('(/session/@bridged)[1]', 'varchar(max)') AS Bridged,
Ssn.value('(/session/@callerId)[1]', 'bigint') AS CallerId,
Ssn.value('(/session/@sessionId)[1]', 'varchar(max)') AS SessionId,
Ssn.value('(/session/outboundDialString/@dialString)[1]', 'varchar(max)') AS DialString
FROM (SELECT S.s.query('.') AS Ssn
FROM @XML.nodes('/sessions/session') AS S (s)) AS SSnes ;
My first query had /sessions/session, because it was querying data at that level, and didn't use nodes(), it just used @XML.value directly.
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 7, 2012 at 1:57 pm
Yes...I just realized that..Thanks much
“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:58 pm
Give this a try:
SELECT S.value('(@applicationId)[1]', 'int') AS ApplicationID,
S.value('(@startTime)[1]', 'time') AS StartTime,
S.value('(@startDate)[1]', 'date') AS StartDate,
S.value('(@direction)[1]', 'varchar(max)') AS Direction,
S.value('(@token)[1]', 'varchar(max)') AS Token,
S.value('(@bridged)[1]', 'varchar(max)') AS Bridged,
S.value('(@callerId)[1]', 'bigint') AS CallerId,
S.value('(@sessionId)[1]', 'varchar(max)') AS SessionId,
S.value('(outboundDialString/@dialString)[1]', 'varchar(max)') AS DialString
FROM @XML.nodes('/sessions/session') AS Ssn (s);
June 8, 2012 at 12:51 pm
Thanks for your replies. I am able to generate an SSIS package that loops through all the XML files. The issue I have here is the XML source I am using is either letting me select all the columns I need from either the session tag of all the xml files or the columns I need from the outboundDialString tag. But what I want is select columns from both the tags and store it in a single table. Can someone please suggest how to get this done.
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 8, 2012 at 12:55 pm
FYI This is how my .xsd file looks. The ones that i have bolded are the columns I need.
<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="sessions">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="session">
<xs:complexType>
<xs:sequence minOccurs="0">
<xs:element minOccurs="0" name="outboundDialString">
<xs:complexType>
<xs:attribute name="dialString" type="xs:unsignedLong" use="optional" />
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="applicationId" type="xs:unsignedInt" use="optional" />
<xs:attribute name="applicationName" type="xs:string" use="optional" />
<xs:attribute name="startTime" type="xs:time" use="optional" />
<xs:attribute name="startDate" type="xs:date" use="optional" />
<xs:attribute name="type" type="xs:string" use="optional" />
<xs:attribute name="direction" type="xs:string" use="optional" />
<xs:attribute name="durationMinutes" type="xs:decimal" use="optional" />
<xs:attribute name="calledId" type="xs:string" use="optional" />
<xs:attribute name="bridged" type="xs:boolean" use="optional" />
<xs:attribute name="callerId" type="xs:string" use="optional" />
<xs:attribute name="sessionId" type="xs:string" use="optional" />
<xs:attribute name="connectionId" type="xs:unsignedByte" use="optional" />
<xs:attribute name="token" type="xs:string" use="optional" />
<xs:attribute name="parentSessionId" type="xs:string" use="optional" />
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="accountId" type="xs:unsignedShort" use="optional" />
<xs:attribute name="startDate" type="xs:date" use="optional" />
<xs:attribute name="startTime" type="xs:time" use="optional" />
<xs:attribute name="endDate" type="xs:date" use="optional" />
<xs:attribute name="endTime" type="xs:time" use="optional" />
</xs:complexType>
</xs:element>
</xs:schema>
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
June 8, 2012 at 1:19 pm
The sql I sent you should do that. Did you try it?
June 8, 2012 at 1:33 pm
David thanks for the reply..I havent tried that yet because I went through the route of SSIS as I needed to loop through a lot of files.
I think I figured it out I have used the UnionAll transformation to combine both the tags and I used 2 xml sources and I got my data from all my required columns and tags. Thanks for help guys.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
June 8, 2012 at 1:44 pm
Oops that doesnt work either... I apologize for making this thread grow big...but unionall doesnt help...it returns duplicate data as well. Is there a way that I can fix this issue...need ur inputs experts...
Thanks in advance
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
June 8, 2012 at 3:14 pm
Guys I am stuck..need your valuable inputs...Is anyone around?? 😀
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
June 8, 2012 at 3:32 pm
When I run the data you gave us above through the SQL I sent you, I get
the attached results. If this is correct, the the SQL I sent you should work.
If not, what is it missing?
June 10, 2012 at 6:43 pm
David Webb-200187 (6/8/2012)
When I run the data you gave us above through the SQL I sent you, I getthe attached results. If this is correct, the the SQL I sent you should work.
If not, what is it missing?
David,
Thanks much for the reply. I have tested it and it works well in my case. But if the input file is a single file then it works well for me. But I need to loop through 30 files so I am creating a SSIS package and I am using a for each container loop to loop through 30 files. It works fine for all the columns of a single child tag but there is a second child tag which has a column which I need to put in the same table. I created 2 xml sources to get the output but unable to use a transformation to get it merged with all the other columns. The union all transformation does it but it gives duplicates too which is not desired. I am unable to think of anything else at this time. Any help is highly appreciated.
Thanks in advance.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
June 10, 2012 at 7:02 pm
Not quite sure what you mean. Can you post some of the XML you have that would exhibit the problem and the results you'd want?
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply