Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2008-10-31
8
Medium Priority
?
3,535 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 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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. 
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

564 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