| Author |
 Topic  |
|
|
xhostx
Constraint Violating Yak Guru
USA
261 Posts |
 Posted - 11/05/2012 : 14:03:26
|
<property name="corp" type="String" nullable="False">
<Wizard ID="sddfhsdhsdksdk'sdkf'g;dsg" Title="Registration" SubmittedBy="" webtId="fed464c0-cdff-4fb7-9acd-c38ee5775b9e">
<Step ID="dfb3c22e-5e24-4b29-bea6-2ded3bacfbb4" Title="sign Now" finished="true">
<Question ID="5900bc7a-7557-4dae-8675-903ebd72e203" RType="SingleTextBox">
<QuestionText>Email</QuestionText>
<Response>myname@yahoo.com</Response>
</Question>
<Question ID="42b21f82-2d8c-432b-a406-9041bc9f7c88" RType="SingleTextBox">
<QuestionText>User</QuestionText>
<Response>myname</Response>
</Question>
</Step>
<Step ID="c4e19b03-08ee-4822-a14b-1a1fc855f2c5" Title="Pers detail" IsComplete="true">
<Question ID="fea6ba9e-a534-4a91-a3c4-f06a0096bbce" RType="SingleTextBox">
<QuestionText>employee</QuestionText>
<Response>company</Response>
</Question>
<Question ID="d338ab86-6fc7-4174-9dbf-c280f13fc17e" RType="">
<QuestionText>Name</QuestionText>
<Response />
<Questions>
<Question ID="8e222260-b2b7-410b-a68a-210eb591d806" RType="SingleTextBox">
<QuestionText>FirstName</QuestionText>
<Response>Alex</Response>
</Question>
<Question ID="9415ea7b-9e6a-4c5b-aea4-c1460752dc11" RType="SingleTextBox">
<QuestionText>Middle</QuestionText>
<Response />
</Question>
<Question ID="42e6c82f-b5ea-413a-84f9-174e5fbed78b" RType="SingleTextBox">
<QuestionText>Last</QuestionText>
<Response>bernard</Response>
</Question>
</Questions>
</Question>
</wizrad>
</property>
I have the above XML stored in an SQL server table column
I need to extract the FirstName and LastName values, in this cases tags responses.
sample:
FirstName LastName
Alex bernard
this is what I have as of now:
select cast(columname as XML).query('for $p in //entity where $p//QuestionText return <property>
<Wizard>
<Questions><QuestionText>
{$p//Response}</QuestionText></Questions>
</Wizard>
</property>')
from dbo.tablename
any help will be appreciated!
--------------------------
Joins are what RDBMS's do for a living |
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/05/2012 : 14:42:35
|
Your XML is not well-formed, so I could not really test this, but hopefully this should work.
If it does not work, change the first cross apply as precise as you can by giving the exact xpath instead of navigating the wildcard '//Questions'.
If that does not do it, can you post your exact (well-formed) XML that someone can cut and paste?SELECT
cl.value('(./Response)[1]','VARCHAR(128)') AS Lastname,
cf.value('(./Response)[1]','VARCHAR(128)') AS Firstname
FROM
YourTable y
CROSS APPLY y.YourXmlColumn.nodes('//Questions') T(c)
CROSS APPLY c.nodes('Question[./QuestionText="Last"]') Tl(cl)
CROSS APPLY c.nodes('Question[./QuestionText="FirstName"]') Tf(cf)
|
Edited by - sunitabeck on 11/05/2012 14:43:04 |
 |
|
|
xhostx
Constraint Violating Yak Guru
USA
261 Posts |
Posted - 11/05/2012 : 15:10:22
|
Thanks man for the try, but None of it works...I think im facing some errors on the syntax!!
it didnt not like the T(c)
Any idea please?
--------------------------
Joins are what RDBMS's do for a living |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/05/2012 : 15:26:19
|
I don't think it is the syntax.
As I said in my earlier post, I am unable to test because the XML you posted is not well-formed.
A random example that I constructed by cutting and pasting from your posting works as expected - see below:CREATE TABLE #tmp(xmlcol XML);
INSERT INTO #tmp VALUES
('<Questions>
<Question ID="8e222260-b2b7-410b-a68a-210eb591d806" RType="SingleTextBox">
<QuestionText>FirstName</QuestionText>
<Response>Alex</Response>
</Question>
<Question ID="9415ea7b-9e6a-4c5b-aea4-c1460752dc11" RType="SingleTextBox">
<QuestionText>Middle</QuestionText>
<Response />
</Question>
<Question ID="42e6c82f-b5ea-413a-84f9-174e5fbed78b" RType="SingleTextBox">
<QuestionText>Last</QuestionText>
<Response>bernard</Response>
</Question>
</Questions>');
SELECT
cl.value('(./Response)[1]','VARCHAR(128)') AS Lastname,
cf.value('(./Response)[1]','VARCHAR(128)') AS Firstname
FROM
#tmp y
CROSS APPLY y.xmlcol.nodes('//Questions') T(c)
CROSS APPLY c.nodes('Question[./QuestionText="Last"]') Tl(cl)
CROSS APPLY c.nodes('Question[./QuestionText="FirstName"]') Tf(cf)
DROP TABLE #tmp; |
Edited by - sunitabeck on 11/05/2012 15:26:43 |
|
|
|
xhostx
Constraint Violating Yak Guru
USA
261 Posts |
Posted - 11/05/2012 : 16:22:18
|
Heeeey Thanks a LOT LOT LOT man!!
It worked well.....very apprecaited.
--------------------------
Joins are what RDBMS's do for a living |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/05/2012 : 19:29:30
|
| very welcome. |
|
|
|
xhostx
Constraint Violating Yak Guru
USA
261 Posts |
Posted - 11/06/2012 : 13:30:24
|
I'm trying to read the email but it seems that NOT inside of <Questions> where the first and Last names are.
It is located in the Tag <Question>? Any hints on that please because I tried to do it myself but didnt probabaly get the logic of Cross apply.
Appreciated
--------------------------
Joins are what RDBMS's do for a living |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/06/2012 : 14:28:46
|
| Can you post a representative sample of the XML? In generatal, you would need to navigate to the node of which the lastname and firstname are child nodes. So you would replace the '//Question' with whatever is required to navigate to that level from the top node. Without seeing the XML, I don't know how to be more specific. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48012 Posts |
Posted - 11/06/2012 : 14:44:42
|
quote:
Originally posted by xhostx
I'm trying to read the email but it seems that NOT inside of <Questions> where the first and Last names are.
It is located in the Tag <Question>? Any hints on that please because I tried to do it myself but didnt probabaly get the logic of Cross apply.
Appreciated
--------------------------
Joins are what RDBMS's do for a living
Sounds like ita an attribute then in which case you need to use like
SELECT
cl.value('./Response)[1]','VARCHAR(128)') AS Lastname,
cf.value('./Response)[1]','VARCHAR(128)') AS Firstname,
c.value('./Question/@Email')
FROM
#tmp y
CROSS APPLY y.xmlcol.nodes('//Questions') T(c)
CROSS APPLY c.nodes('Question[./QuestionText="Last"]') Tl(cl)
CROSS APPLY c.nodes('Question[./QuestionText="FirstName"]') Tf(cf)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Read Xml values
