Author |
Topic |
xhostx
Constraint Violating Yak Guru
277 Posts |
Posted - 2012-11-05 : 14:03:26
|
[code]<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 columnI need to extract the FirstName and LastName values, in this cases tags responses.sample:FirstName LastNameAlex bernard[/code]this is what I have as of now:[code]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[/code]any help will be appreciated!--------------------------Joins are what RDBMS's do for a living |
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-05 : 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 FirstnameFROM 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) |
|
|
xhostx
Constraint Violating Yak Guru
277 Posts |
Posted - 2012-11-05 : 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
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-05 : 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 FirstnameFROM #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; |
|
|
xhostx
Constraint Violating Yak Guru
277 Posts |
Posted - 2012-11-05 : 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
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-05 : 19:29:30
|
very welcome. |
|
|
xhostx
Constraint Violating Yak Guru
277 Posts |
Posted - 2012-11-06 : 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
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-06 : 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
52326 Posts |
Posted - 2012-11-06 : 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 likeSELECT 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|