March 5, 2015 at 1:34 pm
First, welcome to SSC! The sample data you provided was very helpful. In the future try to include some DDL to create the table you are working with. Today I did that for you and created some DDL for you.
-- using tempdb
USE tempdb
GO
IF OBJECT_ID('tempdb.dbo.tblOrder') IS NOT NULL DROP TABLE tblOrder;
GO
CREATE TABLE dbo.tblOrder(xid int identity primary key, zPaymentData xml not null);
GO
INSERT INTO dbo.tblOrder (zPaymentData)
VALUES
-- record #1 has a CreditCardResult node
(
'<PaymentData xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="Com.Commerce.Data/PaymentData.xsd">
<CreditCardResult Code="0" Message="APPROVED" Last4="xxxx" AuthorizationNumber="123456" Amount="6.34000000" />
</PaymentData>'
),
-- record #2 does not have a CreditCardResult node
(
'<PaymentData xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="Com.Commerce.Data/PaymentData.xsd">
<blah/>
</PaymentData>'
)
You were very, very close; you just needed to identify the XML schema for the node... You can also use *: to say "All schemas" (which is often what I do because I am lazy). Using the sample data above, this will do the trick:
SELECT *
FROM dbo.tblOrder
WHERE zPaymentData.exist('//*:CreditCardResult')=1
On a side note: the query above will return a record where the node exists anywhere in the XML structure. If you wanted to return records where PaymentData has a child node named CreditCardResult then you would do this:
SELECT *
FROM dbo.tblOrder
WHERE zPaymentData.exist('/*:PaymentData/*:CreditCardResult')=1
Edit: added second query.
-- Itzik Ben-Gan 2001
March 5, 2015 at 1:48 pm
... and for a bonus answer. Because of the XML data structures/schemas I deal with I can get away with the lazy guy technique of "*:". This is not a Best practice though. The best practice would be to declare and reference the namespace like so:
Using the sample data from my previous post...
WITH XMLNAMESPACES ('Com.Commerce.Data/PaymentData.xsd' AS NS)
SELECT *
FROM dbo.tblOrder
WHERE zPaymentData.exist('/NS:PaymentData/NS:CreditCardResult')=1;
OR (my preference when declaring namespaces)...
SELECT *
FROM dbo.tblOrder
WHERE zPaymentData.exist('declare namespace NS="Com.Commerce.Data/PaymentData.xsd"; /NS:PaymentData/NS:CreditCardResult')=1;
-- Itzik Ben-Gan 2001
March 5, 2015 at 2:24 pm
Hey Alan, this is PERFECT thank you so much!
Can't believe it was that simple.
Because this is just gathering data from a dev server, it can be dirty and take a half hour, not trying to build a production query, but your information on declaring the namespace is extremely helpful.
Thank you again for your help and quick response!!
March 5, 2015 at 2:37 pm
No problem. I too have been there ripping my hair out on SQL-XML namespace issues. 😉
-- Itzik Ben-Gan 2001
Viewing 4 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply