?
Solved

SQL Query

Posted on 2011-09-23
13
Medium Priority
?
243 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
[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
  • 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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 

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 Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

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…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

771 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