Read Xml values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Read Xml values

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 column
I need to extract the FirstName and LastName values, in this cases tags responses.
sample:
FirstName LastName
Alex 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 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)

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 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;

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 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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources