Solved

Using a WHERE in a Select Statement in SQL 2005 and Extract Parts of a Node Field

Posted on 2010-09-03
3
236 Views
Last Modified: 2012-05-10
I have a couple of questions regarding XML and doing a select query in SQL Server 2005.  I need to extract some parts of a field and I need to end my query with a  WHERE clause.

I first insert the XML file into a table like this:

INSERT INTO OrdersXML(xmlCol)
SELECT CONVERT (xml, A.Col1, 2)
FROM OPENROWSET( BULK '\\myfolder\Orders.xml', SINGLE_BLOB) AS A(Col1)

This is a sample of the CustomerInfo field containing the customer info.  I need to extract the First Name, Last name, and Customer Number from this field.  


<CustomerInfo>First Name = John
Last Name = Smith
CustomerNumber = 123456
shippingID = 654321
</CustomerInfo>

In this field, the labels are always going to be the same

First Name =
Last Name =
CustomerNumber =
shippingID =
 
Only the actual name, cust. number and ID changes. Also, notice there's a line break for each part.

And this is my select statement.

SELECT  
c.value('(Shipping/Products/Product/CustomerInfo)[1]', 'varchar(200)'),--I need the First selected.
c.value('(Shipping/Products/Product/CustomerInfo)[1]', 'varchar(200)'),-- I need the Last Name selected.
c.value('(Shipping/Products/Product/CustomerInfot)[1]', 'varchar(200)'),-- I need the Customer Number selected.
c.value('(Shipping/Products/Product/SKU)[1]', 'varchar(10)'), -- This is will part of the WHERE statement.
c.value('(Billing/NameParts/FirstName)[1]', 'varchar(50)'),
c.value('(Billing/NameParts/LastName)[1]', 'varchar(50)'),  
c.value('(Billing/Address/Street1)[1]', 'varchar(50)'),
c.value('(Billing/Address/Street2)[1]', 'varchar(50)'),
c.value('(Billing/Address/City)[1]', 'varchar(50)'),
c.value('(Billing/Address/State)[1]', 'varchar(2)'),
c.value('(Billing/Address/Code)[1]', 'varchar(6)'),  
c.value('(Billing/Address/Country)[1]', 'varchar(10)'),
c.value('(Payment/CreditCard/Issuer)[1]', 'varchar(50)'),
c.value('(OrderDate)[1]', 'datetime'),
c.value('(ShopSiteTransactionID)[1]', 'varchar(300)'),
c.value('(OrderNumber)[1]', 'int'),
c.value('(Other/Comments)[1]', 'varchar(300)')
FROM OrdersXML CROSS APPLY XMLCol.nodes('AllOrders/Order') T(c)

Right now, if I run this select query I can get all the records.  However, I need to be able to select only the records containing a certain SKU, so I will need a WHERE clause that looks for a particular SKU value. I don't know how to make this work.

Therefore, my selected records would like this:

John, Smith, 123456, BMW302, Donald, Trump, 1234 Hollywood Blvd, Los Angeles, ETC.

Does anyone knows how to accomplish this?

Thanks!




0
Comment
Question by:TheUndecider
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 24

Accepted Solution

by:
DBAduck - Ben Miller earned 500 total points
ID: 33598589
Have you tried to use the WHERE with the statement you flagged?
SELECT  
c.value('(Shipping/Products/Product/CustomerInfo)[1]', 'varchar(200)'),--I need the First selected.
c.value('(Shipping/Products/Product/CustomerInfo)[1]', 'varchar(200)'),-- I need the Last Name selected.
c.value('(Shipping/Products/Product/CustomerInfot)[1]', 'varchar(200)'),-- I need the Customer Number selected.
c.value('(Shipping/Products/Product/SKU)[1]', 'varchar(10)'), -- This is will part of the WHERE statement.
c.value('(Billing/NameParts/FirstName)[1]', 'varchar(50)'),
c.value('(Billing/NameParts/LastName)[1]', 'varchar(50)'),  
c.value('(Billing/Address/Street1)[1]', 'varchar(50)'),
c.value('(Billing/Address/Street2)[1]', 'varchar(50)'),
c.value('(Billing/Address/City)[1]', 'varchar(50)'),
c.value('(Billing/Address/State)[1]', 'varchar(2)'),
c.value('(Billing/Address/Code)[1]', 'varchar(6)'),  
c.value('(Billing/Address/Country)[1]', 'varchar(10)'),
c.value('(Payment/CreditCard/Issuer)[1]', 'varchar(50)'),
c.value('(OrderDate)[1]', 'datetime'), 
c.value('(ShopSiteTransactionID)[1]', 'varchar(300)'),
c.value('(OrderNumber)[1]', 'int'),
c.value('(Other/Comments)[1]', 'varchar(300)')
FROM OrdersXML CROSS APPLY XMLCol.nodes('AllOrders/Order') T(c)
WHERE c.value('(Shipping/Products/Product/SKU)[1]', 'varchar(10)') = 'SKUVALUE'

Open in new window

0
 

Author Comment

by:TheUndecider
ID: 33599713
Hello dbaduck,

Thanks for replying.  I believe you're in the right path.  I tried what you suggested at first it did not work because I was looking for a particular SKU in a order containing 3 products.  It was the second one.

Therefore, if I modify the where clause to:

WHERE c.value('(Shipping/Products/Product/SKU)[2]', 'varchar(10)') = 'SKUVALUE'

I guess I was not couting on the fact that someone can order more than one product in the same orderl.  

How can I select the records based on All the products instead of the orders?  I assuming the [1]  in the Shipping/Products/Product/SKU)[1] will have to change to some kind  of variable.
0
 

Author Comment

by:TheUndecider
ID: 33600442
Hi again,

I kept on going on based on your suggestion and I was able to do the proper WHERE clause.  

I figured out products are the fields I needed to base my query on because those are the ones that can be more than one.

therefore, I changed the FROM statement to

    FROM OrdersXML CROSS APPLY XMLCol.nodes('AllOrders/Order/Shipping/Products/Product') T(c)

and changed my WHERE statement to:

   WHERE (c.value('(SKU)[1]', 'varchar(10)')='GoodSKU')

Then, I changed the rest of the nodes from a c.value....

   c.value('(Billing/NameParts/FirstName)[1]', 'varchar(50)'),

to a query:

   c.query('../../../Billing/NameParts/FirstName').value('.', 'varchar(25)'),

Finally, I used SUBSTRING and CHARINDEX to extract the values from the CustomerInfo field.

I know I kinda did it on my own, but still.. you pointed me on the right direction and I am grateful for it.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

631 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