November 16, 2011 at 12:36 pm
I am new to xml and have been banging my head against the wall on this one. It's probably something simple, this is just my first experience with xml inside sql server and have only found enough information online to get me part of the way there. The below sql script has the table definitions, sample data, and desired output. Basically I have a table with an xml column that I need to query and insert the results into a separate table.
--Create a table to store xml document
create table xml_doc(xml_data xml not null)
--Insert xml into the table
--This is similiar to the xml structure I'm working with
insert into xml_doc(xml_data)
select
'<Feed>
<Product id="111">
<Review>4</Review>
<Ratings>
<Overall>3.2</Overall>
<Average>3.5</Average>
</Ratings>
</Product>
<Product id="222">
<Review>10</Review>
<Ratings>
<Overall>2.9</Overall>
<Average>2.7</Average>
</Ratings>
</Product>
<Product id="333">
<Review>6</Review>
<Ratings>
<Overall>4.7</Overall>
<Average>4.5</Average>
</Ratings>
</Product>
</Feed>'
--I need to query this xml column and insert the results into a product table
--Here's the table definition
create table Product (ProductId int primary key,Review int not null,OverallRating decimal(4,2) not null,AverageRating decimal(4,2) not null)
--This is the desired output with the above xml example
insert into Product (ProductID,Review,OverallRating,AverageRating)
select 111,4,3.2,3.5
union all
select 222,10,2.9,2.7
union all
select 333,6,4.7,4.5
select * from product
I know I can query the xml nodes like this, but I don't know how to get them all in one select. I can create a temp table and fill the table with a bunch of different select statements and then do one select from the temp table into my final product table but I'm thinking there has to be a way to get them all in one step.
SELECT ref.value('Review[1]','INT') AS 'Review'
from dbo.xml_doc
cross apply xml_data.nodes('//Feed/Product') AS T(ref)
Also, I can't seem to get the query for the id attribute right. This is close but not exactly what I want.
select xml_data.query('data(//Feed/Product/@id)') as 'productid'
from xml_doc
Getting an answer to my question is great but my real goal is to learn something here. Thanks for any and all help/advice.:-)
November 16, 2011 at 2:47 pm
You're most of the way there. This should give you what you want. The vast majority of the time, you'll probably want to use relative paths for everything except the initial path specification.
SELECT
ref.value('@id', 'INT') AS Product_ID
,ref.value('Review[1]','INT') AS Review
,ref.value('(Ratings/Overall)[1]', 'decimal(5,2)') AS Overall
,ref.value('(Ratings/Average)[1]', 'decimal(5,2)') AS Average
from #xml_doc
cross apply xml_data.nodes('//Feed/Product') AS T(ref)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 16, 2011 at 2:52 pm
Thanks Drew, that gives me what I need. Could have sworn I tried something similar to that but it bombed out. I probably had one of the paths wrong. I will give this a go. Thanks again!!
November 17, 2011 at 6:57 am
The .value() function requires a singleton, and you may not have specified the singleton correctly.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 18, 2011 at 11:41 am
Yeah possibly. In any event I finished that query and it's working great so thanks for that. I now haven't a different query that with slightly different xml that has me stumped again. Sample xml is below.
create table xml_doc(xml_data xml not null)
--Insert xml into the table
insert into xml_doc(xml_data)
select
'<Feed>
<Product id="111">
<Reviews>
<Review>1</Review>
<Review>2</Review>
</Reviews>
</Product>
<Product id="222">
<Reviews>
<Review>3</Review>
<Review>2</Review>
</Reviews>
</Product>
<Product id="333">
<Reviews>
<Review>1</Review>
<Review>3</Review>
<Review>2</Review>
<Review>4</Review>
</Reviews>
</Product>
</Feed>'
I don't believe I can use the value method because it requires the singleton like you mentioned above and I need each <Review> for every product id (sample output is below). Not just the first one. Unless there's a different way to write it? Or is there a different xml method that I have to use to get this to work?
if object_id('tempdb..#output') is not null
drop table #output
--sample output
create table #output (productid int,review int)
insert into #output (productid,review)
select 111,1
union all
select 111,2
union all
select 222, 3
union all
select 222,2
union all
select 333,1
union all
select 333,3
union all
select 333,2
union all
select 333,4
select * from #output
--This obviously doesn't work
select
ref.value('(/Feed/Product/@id)[1]','INT') AS ProductID,
ref.value('(Review)[1]','INT') AS 'Reviews'
from dbo.xml_doc
cross apply xml_data.nodes('/Feed/Product/Reviews') as T(ref)
--Same with this
select
ref.value('(/Feed/Product/@id)[1]','INT') AS ProductID,
ref.value('(Reviews/Review)[1]','INT') AS 'Reviews'
from dbo.xml_doc
cross apply xml_data.nodes('/Feed/Product/Reviews/Review') as T(ref)
November 18, 2011 at 12:32 pm
Remember what i said about relative paths? All of your paths are absolute. (They start with a '/'.) You need to use relative paths. The '.', '..', and any attribute references are already singletons, so you don't need to specify the index ([1]) if you are only going up the hierarchy.
select
ref.value('../../@id','INT') AS ProductID,
ref.value('.','INT') AS 'Reviews'
from #xml_doc
cross apply xml_data.nodes('/Feed/Product/Reviews/Review') as T(ref)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 18, 2011 at 12:56 pm
Yeah, I saw that but I wasn't completely sure what you meant. Should have asked. Thanks again for the help!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply