SQL Query

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

wademiAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
wademiConnect With a Mentor Author Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
wademiAuthor Commented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Kevin CrossChief Technology OfficerCommented:
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
 
wademiAuthor Commented:
The second solution didnt work. I tried that before
0
 
wademiAuthor Commented:
The first solution yielded no results
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
wademiAuthor Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
wademiAuthor Commented:
You are correct. Id 1 is from the same row
0
 
wademiAuthor Commented:
should be

ID MyData
1  Apple
1  Orange
0
 
wademiAuthor Commented:
Resolve it on own
0
 
Kevin CrossChief Technology OfficerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.