troubleshooting Question

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

Avatar of TheUndecider
TheUndeciderFlag for United States of America asked on
XMLMicrosoft SQL Server 2005
3 Comments1 Solution290 ViewsLast Modified:
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!




ASKER CERTIFIED SOLUTION
DBAduck - Ben Miller
SQL Server Architect

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros