Solved

SQL Query

Posted on 2011-09-23
13
227 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

830 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