?
Solved

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

Posted on 2008-10-31
8
Medium Priority
?
3,528 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 1000 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Many times as a report developer I've been asked to display normalized data such as three rows with values Jack, Joe, and Bob as a single comma-separated string such as 'Jack, Joe, Bob', and vice versa.  Here's how to do it. 
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

764 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