?
Solved

SQL Query

Posted on 2011-09-23
13
Medium Priority
?
251 Views
Last Modified: 2012-05-12
I have a table(Fruit) with a data field(Mydata) in my database that stores data like the following.

  <TestFilter>
      <Filter>Apple</Filter>
      <EntityType>CP</EntityType>
    </TestFilter>
    <TestFilter>
      <Filter>Orange</Filter>
      <EntityType>CP</EntityType>
    </TestFilter>



I need a query that loops throught the <Filter> tag in then Mydata field and out ouputs the result.

Like:


ID MyData
1  Apple
2  Orange

0
Comment
Question by:wademi
  • 8
  • 5
13 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36589493
Does the order of ID matter? You can use something like this, though:
SELECT ROW_NUMBER() OVER(ORDER BY T.F.value('.', 'VARCHAR(50)')) ID
     , T.F.value('.', 'VARCHAR(50)') AS MyData
FROM Fruit
CROSS APPLY Fruit.Mydata.nodes('//Filter') T(F)
;

Open in new window

0
 

Author Comment

by:wademi
ID: 36589524
Thansk mwvisa1: The id is already a field in the table....Id,Mydata
How do I structure the query if id is already a field
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36589551
Just remove the ROW_NUMBER() OVER(ORDER BY T.F.value('.', 'VARCHAR(50)')) part. :)
Additionally, if the data is really just a singular Filter per row, then this will probably do:

SELECT ID
     , Fruit.MyData.value('/TestFilter[1]/Filter[1]', 'VARCHAR(50)') AS MyData
FROM Fruit

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:wademi
ID: 36589581
The second solution didnt work. I tried that before
0
 

Author Comment

by:wademi
ID: 36589600
The first solution yielded no results
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36589617
Then, please show a more accurate picture of your data as both solutions were tested and work based on the XML you provided. If that field is not XML, then please advise what the table structure is as well as providing sample data. Additionally, please provide a definition of what "didnt work" means, i.e., error message, no results, etc.
0
 

Author Comment

by:wademi
ID: 36589646
Sorry the Mydat field is an XML type. Here is a sample record

<AdminProfile xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/Vevo.Data.Admin.Security">
  <allowed>
 <TestFilter>
      <Filter>Apple</Filter>
      <EntityType>CP</EntityType>
    </TestFilter>
    <TestFilter>
      <Filter>Orange</Filter>
      <EntityType>CP</EntityType>
    </TestFilter>
  </allowed>
  <AllowedCountries>
    <Country xmlns="">
      <CountryCode>*</CountryCode>
      <Name>*</Name>
    </Country>
  </AllowedCountries>
  <AllowedCultures>
    <Culture xmlns="">
      <LanguageCode>*</LanguageCode>
      <Name>*</Name>
    </Culture>
  </AllowedCultures>
</AdminProfile>
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36589678
You are once again showing Apple and Orange in the same XML value, i.e., one row's data of MyData column. If that is the case, then how does ID change to 2. If it is 1 for the first row, what will happen is you will get 1 repeated and the different values of Filter if there are multiple. The reason it is likely not working for you is that the structure is a bit different than showed, plus you have a namespace involved. I will have to test, but you will at least have to account for <allowed> and <AdminProfile> since <TestFilter> is not truly the root node.
0
 

Author Comment

by:wademi
ID: 36589786
You are correct. Id 1 is from the same row
0
 

Author Comment

by:wademi
ID: 36589827
should be

ID MyData
1  Apple
1  Orange
0
 

Accepted Solution

by:
wademi earned 0 total points
ID: 36589997
I got it:

WITH XMLNAMESPACES (DEFAULT 'http://schemas.datacontract.org/2004/07/mydb.Data.Admin.Security')

SELECT id, X.Loc.query('
for $a in .
where $a/EntityType = "filter"
return $a/Filter      
').value('.', 'varchar(max)')
FROM fruit
CROSS APPLY Profile.nodes('AdminProfile/Allowed/TestFilter') as X(Loc)
0
 

Author Closing Comment

by:wademi
ID: 36715414
Resolve it on own
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36590015
Hmm. None of my comments led you to that solution? Anyway, I am glad you got it working.
Best regards and happy coding,

Kevin
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
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…

864 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