TSQL Script to Find the Names of Stored Procedures that Use XQUERY

Below scripts can be used to find the names of Stored Procedures that use XQUERY : –Method 1: SELECT  SP_Name StoredProcedure FROM (SELECT schema_name(schema_id)+’.’+name SP_Name FROM sys.procedures ) tmpCROSS APPLY sys.dm_sql_referenced_entities (SP_Name, ‘OBJECT’)WHERE referenced_entity_name IN ( ‘value’,’query’)AND is_ambiguous = 1 –Method 2: SELECT schema_name(schema_id)+’.’+object_name(referencing_id) StoredProcedureFROM sys.sql_expression_dependencies  SEDJOIN sys.procedures P ON P.object_id = SED.referencing_idWHERE referenced_entity_name IN…

Details

SQL Server – Capturing Missing Join Predicate for queries using Extended Events

missing_join_predicate , one of the event  –  ” Occurs when an executed query is missing a join predicate. Use this event to identify a query that may perform slowly because of the missing predicate. This event only occurs if both sides of the join return more than one row ” . SELECT * FROM sys.dm_xe_objects…

Details

Search for a string in XML column of a table

This article has the examples on how to search for a string in XML column  of a table . Consider below sample data : DECLARE @StudentInfo TABLE (Id INT, Name VARCHAR(20),Subjects XML)INSERT @StudentInfo SELECT 1,’Sathya’,‘<Subjects>    <Subject>      <Name>English</Name>      <Marks>70</Marks>    </Subject>    <Subject>     <Name>Maths</Name>     <Marks>60</Marks>    </Subject></Subjects>’INSERT @StudentInfo SELECT 2,’Deepak’,‘<Subjects>    <Subject>      <Name>English</Name>      <Marks>70</Marks>    </Subject>    <Subject>     <Name>Science</Name>     <Marks>60</Marks>    </Subject></Subjects>’SELECT *…

Details

SQL Server – XML Examples(Xpath,Xquery,XML DML,XML Index,XML Schema)

Example 1: Mostly we need  XML in this format,i.e column names with their values enclosed within their column name tag Consider below XML needs to be generated for the below mentioned sample table definition <Employee> <field Name=“ID“>1</field><field Name=“Name“>Sathya</field><field Name=“Age“>25</field><field Name=“Sex“>Male</field><field Name=“ID“>2</field><field Name=“Name“>Sunny</field><field Name=“Age“>24</field><field Name=“Sex“>Female</field></Employee> DECLARE @Employee TABLE (ID INT,Name VARCHAR(100),Age INT,Sex VARCHAR(50)) INSERT @Employee SELECT 1,‘Sathya’,25,‘Male’INSERT…

Details

XQuery – Singleton Error

value( )  method always returns a scalar value of specified SQL Server data type.Example: for value ( ) method & how to avoid singleton error. CREATE DATABASE TEST_XML USE TEST_XMLGO CREATE TABLE College_Master (College_ID INT NOT NULL, College_Name VARCHAR(20),College_Details XML) INSERT INTO College_Master VALUES (1,’BCC UNIVERSITY’,'<STUDENTINFO>  <student ID=”1″ name=”Sathya”>    <subject ID=”1″ Name=”Electronics and Communication” />   …

Details

XQUERY,XPATH,XMLSCHEMA,XML INDEX

(XQUERY,XPATH,XMLSCHEMA,XML INDEX) Ø  XQuery is the language for querying XML data.    Ø  XPath was designed to navigate an XML document to retrieve the documents elements and attributes.      Ø  XQuery is built on XPath expressions. Ø To put it simple XQuery,XPath are used to traverse through XML document/fragment to fetch/modify attribute values or element…

Details