March 15, 2011 at 8:48 pm
Hi there,
XML is not my speciality so would love a little bit of help if anyone has a free moment. I have the following XML code which I need to break out into a relational format over three tables, stores, products and product ranks:
<Root>
<Store>
<StoreName>Store 1</StoreName>
<Product>
<Key>0</Key>
<Name>Product 1</Name>
<Ranks count="5">
<Rank>
<Points>1</Points>
<Description>Description 1</Description>
</Rank>
<Rank>
<Points>2</Points>
<Description>Description 2</Description>
</Rank>
<Rank>
<Points>3</Points>
<Description>Description 3</Description>
</Rank>
<Rank>
<Points>4</Points>
<Description>Description 4</Description>
</Rank>
<Rank>
<Points>5</Points>
<Description>Description 5</Description>
</Rank>
</Ranks>
</Product>
<Product>
<Key>1</Key>
<Name>Product 2</Name>
<Ranks count="4">
<Rank>
<Points>1</Points>
<Description>Description 1</Description>
</Rank>
<Rank>
<Points>2</Points>
<Description>Description 2</Description>
</Rank>
<Rank>
<Points>3</Points>
<Description>Description 3</Description>
</Rank>
<Rank>
<Points>4</Points>
<Description>Description 4</Description>
</Rank>
</Ranks>
</Product>
</Store>
</Root>
Essentially there is one or more stores and within those stores are zero or more products and then within those products there are zero or more ranks. I can create a stores table, products table but I can't link each products ranking to their own particular products.
In my XML above there is one store which has two products. The first product has 5 ranks and the second product has 4 ranks. I'm using the following code to pull the stores and associated products which works well:
SELECT stores.detail.value('(StoreName)[1]', 'varchar(50)') AS store_name
, store_products.detail.value('(Name)[1]', 'varchar(50)') AS product_name
FROM dbo.sample_table
CROSS APPLY sample_table.raw_xml.nodes('/Root/Store') AS stores(detail)
CROSS APPLY sample_table.raw_xml.nodes('/Root/Store/Product') AS store_products(detail)
The above query returns two records, both for store 1 and for product 1 and 2. Now I want to pull the descriptions of the ranks. I can't use the same CROSS APPLY method to link in these rank descriptions as I end up with a many to many join which returns 18 records and I only want 9 records, 5 for product 1 and 4 for product 2:
SELECT stores.detail.value('(StoreName)[1]', 'varchar(50)') AS store_name
, store_products.detail.value('(Name)[1]', 'varchar(50)') AS product_name
, store_product_ranks.detail.value('(Description)[1]', 'varchar(50)') AS product_rank_description
FROM dbo.sample_table
CROSS APPLY sample_table.raw_xml.nodes('/Root/Store') AS stores(detail)
CROSS APPLY sample_table.raw_xml.nodes('/Root/Store/Product') AS store_products(detail)
CROSS APPLY sample_table.raw_xml.nodes('/Root/Store/Product/Ranks/Rank') AS store_product_ranks(detail)
So I know CROSS APPLY is not going to work for me but I'm unsure what to do. My end state is generating a stores table, products table and then product ranks table. Then they would all be linked together in a relational format.
If I can get away without using OPENXML that would be good as I've not heard complimentary things about it. You can use the following SQL code to generate and populate the sample table to see my problem:
IF EXISTS (SELECT 1
FROM sys.objects
WHERE OBJECT_ID = OBJECT_ID(N'dbo.sample_table'))
BEGIN
DROP TABLE dbo.sample_table
END
CREATE TABLE dbo.sample_table (raw_xml XML)
INSERT INTO dbo.sample_table (raw_xml)
SELECT
'<Root>
<Store>
<StoreName>Store 1</StoreName>
<Product>
<Key>0</Key>
<Name>Product 1</Name>
<Ranks count="5">
<Rank>
<Points>1</Points>
<Description>Description 1</Description>
</Rank>
<Rank>
<Points>2</Points>
<Description>Description 2</Description>
</Rank>
<Rank>
<Points>3</Points>
<Description>Description 3</Description>
</Rank>
<Rank>
<Points>4</Points>
<Description>Description 4</Description>
</Rank>
<Rank>
<Points>5</Points>
<Description>Description 5</Description>
</Rank>
</Ranks>
</Product>
<Product>
<Key>1</Key>
<Name>Product 2</Name>
<Ranks count="4">
<Rank>
<Points>1</Points>
<Description>Description 1</Description>
</Rank>
<Rank>
<Points>2</Points>
<Description>Description 2</Description>
</Rank>
<Rank>
<Points>3</Points>
<Description>Description 3</Description>
</Rank>
<Rank>
<Points>4</Points>
<Description>Description 4</Description>
</Rank>
</Ranks>
</Product>
</Store>
</Root>'
SELECT stores.detail.value('(StoreName)[1]', 'varchar(50)') AS store_name
, store_products.detail.value('(Name)[1]', 'varchar(50)') AS product_name
, store_product_ranks.detail.value('(Description)[1]', 'varchar(50)') AS product_rank_description
FROM dbo.sample_table
CROSS APPLY sample_table.raw_xml.nodes('/Root/Store') AS stores(detail)
CROSS APPLY sample_table.raw_xml.nodes('/Root/Store/Product') AS store_products(detail)
CROSS APPLY sample_table.raw_xml.nodes('/Root/Store/Product/Ranks/Rank') AS store_product_ranks(detail)
Kind regards,
Chris
March 16, 2011 at 10:15 am
The problem is that you are using absolute paths on the original column when you should be using relative paths on the derived columns.
SELECT stores.detail.value('(StoreName)[1]', 'varchar(50)') AS store_name
, store_products.detail.value('(Name)[1]', 'varchar(50)') AS product_name
, store_product_ranks.detail.value('(Description)[1]', 'varchar(50)') AS product_rank_description
FROM sample_table
CROSS APPLY sample_table.raw_xml.nodes('/Root/Store') AS stores(detail)
CROSS APPLY stores.detail.nodes('Product') AS store_products(detail)
CROSS APPLY store_products.detail.nodes('Ranks/Rank') AS store_product_ranks(detail)
So the second CROSS APPLY uses the derived column "stores.detail" instead of starting at the top with "sample_table.raw_xml" and it also uses the relative path "Product" rather than the absolute path "/Root/Store/Product". A similar change to the second CROSS APPLY should give you the desired results.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 16, 2011 at 12:51 pm
Magic, thanks for that Drew, works like a charm 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply