March 5, 2015 at 1:19 pm
Hello all.. first off, thank you in advance for any help you can provide!
I've been googling now for 2 days and I can't see to figure out this query.
I'm not really an MSSQL expert (I know some MySQL) and now the boss needs a query involving XML.
All I really need to know is how to see if a specific node exists. Here's the XML, as well as the query I have:
<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>
Basically I just need to see if the CreditCardResult node exists, and return that relevant row. That's it!
Here's the where clause so far. I hate asking for help, but I'm pulling my hair out here, I've tried every variation I can think of and checked tons of forums and tech sites.. no luck..
SELECT TOP 1000 * FROM tblOrder
WHERE zPaymentData.exist('(/PaymentData/CreditCardResult[1])') = 1
I know this is likely super simple, I've just never worked with XML before in SQL and apparently I suck at it 🙂
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply