Solved

SQL Query

Posted on 2011-09-23
13
221 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 59

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 59

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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 59

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 59

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 59

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

772 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