asked on

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

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TheUndecider

ASKER

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.

TheUndecider

ASKER

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.