Solved

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

Posted on 2008-10-31
8
3,524 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
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.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

691 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