Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <xml>
- <customer name="Max">
- <email address="me@you.com" />
- </customer>
- <customer name="Erik">
- <email address="erik@your-mom.com" />
- </customer>
- <customer name="Brent">
- <email address="brentcom" />
- </customer>
- </xml>
- <customer name="Brent">
- <email address="brentcom" />
- </customer>
- DECLARE @x XML = '<xml>
- <customer name="Max"><email address="me@you.com" /></customer>
- <customer name="Erik"><email address="erik@your-mom.com" /></customer>
- <customer name="Brent"><email address="brentcom" /></customer>
- </xml>';
- SELECT WithValidEmail = @x.query('/xml/customer/email[contains(@address, "@")]')
- , WithInvalidEmail = @x.query('/xml/customer/email[contains(@address, "@")] = False');
- ╔═══════════════════════════════════════╦══════════════════╗
- ║ WithValidEmail ║ WithInvalidEmail ║
- ╠═══════════════════════════════════════╬══════════════════╣
- ║ <email address="me@you.com" /> ║ ║
- ║ <email address="erik@your-mom.com" /> ║ false ║
- ╚═══════════════════════════════════════╩══════════════════╝
- SELECT WithInValidEmail = @x.query('/xml/customer/email')
- WHERE @x.exist('/xml/customer/email[contains(@address, "@")]') = 0;
- ╔══════════════════╗
- ║ WithInValidEmail ║
- ╚══════════════════╝
- (no results)
- DECLARE @x XML
- = '<xml>
- <customer name="Max"><email address="mvernon@mvct.com" /></customer>
- <customer name="Erik"><email address="erik@your-mom.com" /></customer>
- <customer name="Brent"><email address="brentcom" /></customer>
- </xml>';
- SELECT x.c.value('@address', 'VARCHAR(100)') AS [email]
- FROM @x.nodes('/xml/customer/email') AS x(c)
- WHERE x.c.exist('@address[contains(., "@")]') = 0;
Add Comment
Please, Sign In to add comment