SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Read Xml values
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

xhostx
Constraint Violating Yak Guru

USA
277 Posts

Posted - 11/05/2012 :  14:03:26  Show Profile  Reply with Quote
<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

5155 Posts

Posted - 11/05/2012 :  14:42:35  Show Profile  Reply with Quote
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
Go to Top of Page

xhostx
Constraint Violating Yak Guru

USA
277 Posts

Posted - 11/05/2012 :  15:10:22  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/05/2012 :  15:26:19  Show Profile  Reply with Quote
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
Go to Top of Page

xhostx
Constraint Violating Yak Guru

USA
277 Posts

Posted - 11/05/2012 :  16:22:18  Show Profile  Reply with Quote
Heeeey Thanks a LOT LOT LOT man!!

It worked well.....very apprecaited.



--------------------------
Joins are what RDBMS's do for a living
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/05/2012 :  19:29:30  Show Profile  Reply with Quote
very welcome.
Go to Top of Page

xhostx
Constraint Violating Yak Guru

USA
277 Posts

Posted - 11/06/2012 :  13:30:24  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/06/2012 :  14:28:46  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/06/2012 :  14:44:42  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000