Search

Advertisements

Showing posts with label XQUERY. Show all posts
Showing posts with label XQUERY. Show all posts

SSRS - Make common change in multiple reports in one click

Consider a scenario, say if there are more than 100 reports developed already. Each & every report has company logo on header as embedded image. Suddenly, if the company has decided to make minor change in the logo and all the reports needs to be modified with new logo image.

To make common change in multiple reports in one click, we need to create 
  1. Stored procedure 
  2. SSIS package

 Worked Example:

 I have created six sample reports with "SQL Server 2014" image in the header as shown below:

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 WHERE name = 'missing_join_predicate'

--Create Session to track missing_join_predicate event with below mentioned Actions


SQL Server - Different ways to shred XML into table

 SQL Server - Different ways to shred XML into table :


1.) Using OPENXML or nodes()

2.) SSIS - XML Source task

3.) Powershell  

4.) Using VB/C# script (Script task in SSIS)

5.) Using SQL XML Bulkload API


 Reference links :

SQL Server XML Bulk load examples
http://www.allaboutmssql.com/2013/08/sql-server-xml-bulk-load-examples.html

SQL Server - Import/Shred XML from XML file placed in a folder into table
http://gallery.technet.microsoft.com/scriptcenter/SQL-Server-ImportShred-XML-7f3baa40#content

SSIS - Shred data from XML file in folder into columns of a table
http://www.allaboutmssql.com/2013/08/ssis-shred-data-from-xml-file-in-folder.html

Best Way to Shred XML document and insert the result set into a table in SQL Server
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/52769628-58e3-4d3f-869b-519f61d86da5/best-way-to-shred-xml-document-and-insert-the-result-set-into-a-table-in-sql-server


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 * FROM @StudentInfo







 --To search for subject named science in the XML column
SELECT *
FROM @StudentInfo
WHERE Subjects.exist('Subjects/Subject[Name="Science"]') = 1
--To search for subject named science and marks >= 50 in the XML column
SELECT *
FROM @StudentInfo
WHERE Subjects.exist('Subjects/Subject[Name="Science"][Marks >= "50"]') = 1
--Using Contains function to search for subject named science

SELECT *
FROM @StudentInfo
WHERE Subjects.exist('(Subjects/Subject/Name[contains(.,"Science")])')=1



Related reference links :

http://technet.microsoft.com/en-us/library/ms189869.aspx
http://msdn.microsoft.com/en-us/library/ms178026.aspx
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/601f6859-3567-435b-b764-859fb08a1044/search-for-xml-string





 





SQL Server - Import/Shred XML from XML file placed in a folder into table

If the requirement is to import/shred XML from XML file placed in a folder into table , then this can be done by creating stored procedure using XQUERY & OPENROWSET :

Refer my post on Technet gallery  - http://gallery.technet.microsoft.com/scriptcenter/SQL-Server-ImportShred-XML-7f3baa40#content

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 @Employee SELECT 2,'Sunny',24,'Female'

DECLARE @xmldata XML

SET @xmldata = (SELECT ID,Name,Age,Sex FROM @Employee FOR XML PATH (''))

SET @xmldata = (
SELECT ColumnName AS "@Name",
       ColumnValue AS "text()"
FROM(
SELECT i.value('local-name(.)','varchar(100)') ColumnName,
       i.value('.','varchar(100)') ColumnValue
FROM @xmldata.nodes('//*[text()]') x(i)) tmp
FOR XML PATH ('field'),root('Employee'))

SELECT @xmldata

Example 2:

If XML is stored as NTEXT and if we are required to make changes in the node values of that XML


Sample XML,

<Employee>
<field Name="ID">1</field>
<field Name="Name">Sathya</field>
<field Name="Age">25</field>
<field Name="Sex">Male</field>
</Employee>

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_XML
GO

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" />
    <subject ID="2" Name="Circuit Analysis" />
    <subject ID="3" Name="Mobile Communication" />
  </student>
  <student ID="2" name="Deepak">
    <subject ID="4" Name="Data Structure" />
    <subject ID="5" Name="Java" />
    <subject ID="6" Name="Database Management System" />
  </student>
  <student ID="3" name="sathish">
    <subject ID="7" Name="Soil Mechanics" />
    <subject ID="8" Name="Steel Design" />
    <subject ID="9" Name="Concrete Design" />
  </student>
</STUDENTINFO>')



If you execute below query,

 SELECT College_Details.value('(/STUDENTINFO/student/@name)','varchar(20)') FROM College_Master
WHERE College_ID = 1;


you will get below error,because value ( ) method always returns a scalar value

Msg 2389, Level 16, State 1, Line 1
XQuery [College_Master.College_Details.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'



In the above example ,if you notice XML instance ,it has 3 student element nodes,

So to return first student name,execute below query


SELECT College_Details.value('(/STUDENTINFO/student/@name)[1]','varchar(20)') FROM College_Master
WHERE College_ID = 1;


to return second&third student names,execute below queries,

SELECT College_Details.value('(/STUDENTINFO/student/@name)[2]','varchar(20)') FROM College_Master
WHERE College_ID = 1;

SELECT College_Details.value('(/STUDENTINFO/student/@name)[3]','varchar(20)') FROM College_Master
WHERE College_ID = 1;


 --DROP DATABASE TEST_XML

All about XML in SQL Server

SQL Server handles two types of data

1.)Relational data
2.)XML data

I have covered almost all topics about handling XML in SQL Server and posted in my site.

SECTION - 1:

      How to convert relational data to XML data ?

      answer :       use FOR XML clause

For more information about FOR XML clause, refer below link

http://www.allaboutmssql.com/2013/11/handling-xml-data-in-sql-server.html


SECTION - 2



How to traverse through XML document/fragment to fetch/modify attribute values or element nodes?

answer use XQUERY & XPATH

 How to shred XML data into columns of relational tables? 

 answer : use nodes() &  OPENXML

 How to validate structure & values of XML,each time when it is inserted/updated?

 answer : use XML SCHEMA COLLECTION

 How to improve the performance of querying XML data ?

  answer : use XML INDEX - primary & secondary index.



For more information about XQUERY,XPATH,XMLINDEX,XMLSCHEMA clause, refer below link

http://www.allaboutmssql.com/2012/09/xqueryxpathxmlschemaxml-index_6.html

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 nodes.



XQuery FLWOR Expression:


  • for - (optional) binds a variable to each item returned by the in expression
  • let - (optional) to assign value to variable
  • where - (optional) specifies a criteria
  • order by - (optional) specifies the sort-order of the result
  • return - specifies what to return in the result.


Ø  The xml data type features several built-in methods that allow you to manipulate XML instance data. 

These methods allow you to query, modify, or shred your XML data into relational form.


query()   -The query() method allows you to perform an XQuery on your xml instance.
The result returned is untyped XML. 

Syntax: DbObject.query('XQuery')


value()   - The value() method allows you to perform an XQuery on your xml instance and returns a scalar value cast to a SQL Server data type.

Syntax: DbObject.value('XQuery', 'SqlType')

               
exist() - The exist() method allows you to specify an XQuery on your xml instance and returns a SQL bit value of 1 if the XQuery returns a result, 0 if the XQuery returns no result, or NULL if the xml instance is NULL.

Syntax :DbObject.exist('XQuery')



modify()                 - The modify() method allows you to execute XML Data Manipulation Language (XML DML) statements against an xml instance. The modify() method can only  be used with a SET clause or statement.

Syntax :DbObject.modify('XQuery')


nodes() - The nodes() method allows you to shred xml instances. Shredding is the process of converting your XML data to relational form.

Syntax :DbObject.nodes('XQuery') AS TableAlias(ColumnAlias)



XML DML Keywords:

Delete - Deletes the node specified by an XQuery path expression.

Insert -  Inserts one or more nodes as the children or siblings of a node specified
by an XQuery path expression.

replace value of    -  Updates the value of a node specified by an XQuery path.



--Examples for XQUERY & XPATH

--lets us  create sample data

Advertisements