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

Handling XML data in SQL Server

XML : Extensible Markup Language (XML) has been widely adopted as a platform-independent format for data representation. Before getting deep into XML,a little introduction to the structure of XML. Sample XML fragment: <?xml version = “1.0” encoding = “UTF-16″?> <!—Student Information –> <Studentinfo>   <Student>     <StudentID>1</StudentID>     <StudentName>Sathya</StudentName>     <CourseID>1</CourseID>   </Student>   <Student>…

Details

How to Install SQLXML 4.0 SP1

As mentioned in MSDN , after SQL Server 2008, the latest version of SQLXML (SQLXML 4.0 SP1) is no longer included in SQL Server. To install SQLXML 4.0 SP1 when it is available, download it from  – http://www.microsoft.com/en-gb/download/details.aspx?id=30403 After downloading SQLXML 4.0 , based on the OS (32 / 64 bit) choose the file and…

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