Solved

XPath expression in SQL Express 2008 to return all attribute values?

Posted on 2008-10-31
8
3,511 Views
Last Modified: 2013-11-11
I have some very basic XML in an XML column in a SQL Express 2008 database:

    <Parent>

      <Child ChildName="ChildA">
        <ChildType>Well Behaved</ChildType>
        <OtheChildData>
           ...
        </OtherChildData>
      </Child>

      <Child ChildName="ChildB">
        <ChildType>Spoiled Rotten</ChildType>
        <OtheChildData>
         ...
        </OtherChildData>
      </Child>

    </Parent>

The T-SQL and XPath below will return a table with a single row containing "ChildA"

      declare @Children xml;
      select @Children=ChildXML from ChildTable
      
      
      select @SCFVars=@SCFVars.query('//Parent')
      select @SCFVars.value('(//Parent/@ChildName)[1]','char(35)') as ChildNameCol

Of course - if [2] is used instead of [1], I get a single row containing "ChildB".

Is there a way that I could get a table with all rows?  I have been playing with XPath and keep getting a message that a singleton is required.
0
Comment
Question by:MDKIMZEY
  • 5
  • 3
8 Comments
 
LVL 13

Accepted Solution

by:
sm394 earned 250 total points
ID: 22853827
DECLARE @SearchIn2 XML set @SearchIN2='<Parent>
      <Child ChildName="ChildA">
        <ChildType>Well Behaved</ChildType>
          </Child>
      <Child ChildName="ChildB">
        <ChildType>Spoiled Rotten</ChildType>
             </Child>
    </Parent>'
 
 
 SELECT D.element.value('@ChildName', 'nvarchar(255)')
 FROM @SearchIn2.nodes('/Parent/Child') AS D(element)
0
 

Author Comment

by:MDKIMZEY
ID: 22858254
Do I need to create a table D?  When I try to execute the snippet above, I get the following error message -

Cannot find either "D" pr the user-defined function or aggregate "D.element.value", or the name is ambiguous.
0
 
LVL 13

Expert Comment

by:sm394
ID: 22858720
no you don't need to create anything
i have tested the above code snippet again in Query Analyzer of both SQL SERVER 2005 and SQL Server 2008 it executes and output with any error
0
 
LVL 13

Expert Comment

by:sm394
ID: 22858731
see attached screen
output.jpg
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:MDKIMZEY
ID: 22858742
It works for me as well in SQL Server 2005 - however, in SQL Express 2008 it simply yields the

Cannot find either "D" pr the user-defined function or aggregate "D.element.value", or the name is ambiguous.

Is this not supported by Express?
0
 
LVL 13

Expert Comment

by:sm394
ID: 22858773
well i have sql 2005 express instance installed as well and i access it through 2008 SSMS and it works
0
 

Author Comment

by:MDKIMZEY
ID: 22858793
I am using SQL Express 2008 and SQL Server Developer 2005.

It works without problem on SQL Serve Developer 2005
The error only seems to occur on SQL Express 2008

Is there a SQL Express 2008 Servce Pack?
0
 
LVL 13

Expert Comment

by:sm394
ID: 22858839
hi
i don't see SP1 for SQL Express 2008  on express downloads site
http://www.microsoft.com/express/download/
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now