Solved

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

Posted on 2008-10-31
8
3,521 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 13

Expert Comment

by:sm394
ID: 22858731
see attached screen
output.jpg
0
 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Confluence of Individual Knowledge and the Collective Intelligence At this writing (summer 2013) the term API (http://dictionary.reference.com/browse/API?s=t) has made its way into the popular lexicon of the English language.  A few years ago, …
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

737 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