Retrieving Data - Urgent Pls - 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

Retrieving Data - Urgent Pls

Author

Topic

sital
Yak Posting Veteran

89 Posts

Posted - 2009-01-17 : 05:51:50

Hi all

I want to retrieve only the Skills element from the column that is declared as XML. How can I retrieve a specific element from all the elements? For eg. I have a table in the column that is declared as XML I have the following elements.

First Name
Last Name
Address Line1
Address Line2
City
Skills etc.

From all the elements I would like to retrieve only the skills elements?

How to do it?

I tried doing it by this coding but it is giving error

select Resume.Value('declare namespace ns = "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works">') from HumanResources.JobCandidate

Please note that Resume is the column name which has the datatype XML.

Error Which I get is.
Msg 227, Level 15, State 1, Line 1
"Value" is not a valid function, property, or field.

What should i use instead of value.?

Pls let me know soon..

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-17 : 06:38:38

how is your xml structure? could you please post a sample?

sital
Yak Posting Veteran

89 Posts

Posted - 2009-01-17 : 10:21:39

The sample of XML Structure is:

<ns:Resume xmlns:ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume">
<ns:Name>
<ns:Name.Prefix></ns:Name.Prefix>
<ns:Name.First>Shai</ns:Name.First>
<ns:Name.Middle></ns:Name.Middle>
<ns:Name.Last>Bassli</ns:Name.Last>
<ns:Name.Suffix></ns:Name.Suffix>
</ns:Name>
<ns:Skills>
I am an experienced and versatile machinist who can operate a range of machinery personally as well as supervise the work of other machinists. I specialize in diagnostics and precision inspection, have expertise in reading blueprints, and am able to call on strong interpersonal and communication skills to guide the work of other production machinists whose work I am called upon to inspect.
My degree in mechanical engineering affords me a better theoretical understanding and mathematical background than many other candidates in the machinist trade.
</ns:Skills>
<ns:Employment>
<ns:Emp.StartDate>2000-06-01Z</ns:Emp.StartDate>
<ns:Emp.EndDate>2002-09-30Z</ns:Emp.EndDate>
<ns:Emp.OrgName>Wingtip Toys</ns:Emp.OrgName>
<ns:Emp.JobTitle>Lead Machinist</ns:Emp.JobTitle>
<ns:Emp.Responsibility> Supervised work of staff of four machinists. Coordinated all complex assembly and tooling activities, including production of tricycles and wagons.
Developed parts fabrication from sample parts, drawings and verbal orders.Worked with ISO9000 implementation.
</ns:Emp.Responsibility>
<ns:Emp.FunctionCategory>Production</ns:Emp.FunctionCategory>
<ns:Emp.IndustryCategory>Manufacturing</ns:Emp.IndustryCategory>
<ns:Emp.Location>
<ns:Location>
<ns:Loc.CountryRegion>US </ns:Loc.CountryRegion>
<ns:Loc.State>MI </ns:Loc.State>
<ns:Loc.City>Saginaw</ns:Loc.City>
</ns:Location>
</ns:Emp.Location>
</ns:Employment>

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-17 : 12:57:55

something like

select Resume.query('declare namespace ns = "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works">
data(/ns:Resume/ns:Name.Prefix)') AS Prefix,
Resume.query('declare namespace ns = "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works">
data(/ns:Resume/ns:Name.First)') AS FirstName,
Resume.query('declare namespace ns = "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works">
data(/ns:Resume/ns:Name.Last)') AS LastName 
....
from HumanResources.JobCandidate

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-17 : 12:59:11

[code]
SELECT Resume.value('declare namespace ns=
"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";
(/ns:Resume/ns:Skills)[1]',
'Varchar(max)') AS Skills
FROM Tablename
[/code]

sital
Yak Posting Veteran

89 Posts

Posted - 2009-01-18 : 05:50:03

ThankYou it is working exactly as I wanted.

sital
Yak Posting Veteran

89 Posts

Posted - 2009-01-18 : 05:51:31

quote:
Originally posted by visakh16

something like
select Resume.query('declare namespace ns = "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works">
data(/ns:Resume/ns:Name.Prefix)') AS Prefix,
Resume.query('declare namespace ns = "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works">
data(/ns:Resume/ns:Name.First)') AS FirstName,
Resume.query('declare namespace ns = "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works">
data(/ns:Resume/ns:Name.Last)') AS LastName
....
from HumanResources.JobCandidate

Hi the above query given by you results in an error:

Error:

Msg 9327, Level 16, State 1, Line 1
XQuery [HumanResources.JobCandidate.Resume.query()]: All prolog entries need to end with ';', found '>'.

Follow the query provided by Sodeeep that works out.

sital
Yak Posting Veteran

89 Posts

Posted - 2009-01-18 : 05:51:54

ThankYou it is working exactly as I wanted.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-18 : 08:51:33

quote:
Originally posted by sital

quote:
Originally posted by visakh16

something like
select Resume.query('declare namespace ns = "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works">;
data(/ns:Resume/ns:Name.Prefix)') AS Prefix,
Resume.query('declare namespace ns = "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works">;
data(/ns:Resume/ns:Name.First)') AS FirstName,
Resume.query('declare namespace ns = "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works">;
data(/ns:Resume/ns:Name.Last)') AS LastName
....
from HumanResources.JobCandidate

Hi the above query given by you results in an error:

Error:

Msg 9327, Level 16, State 1, Line 1
XQuery [HumanResources.JobCandidate.Resume.query()]: All prolog entries need to end with ';', found '>'.

Follow the query provided by Sodeeep that works out.

missed ;

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-18 : 10:11:43

quote:
Originally posted by sital

ThankYou it is working exactly as I wanted.

Welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources