Search

Advertisements

Showing posts with label XML. Show all posts
Showing posts with label XML. Show all posts

SQL Server Error - Msg 9412, Level 16, State 1

We will get this error when we are trying to query XML in SQL Server. If that XML is invalid, Say for example if the closing tag ">" is missed in the input XML

Below error message will be throwed :

Msg 9412, Level 16, State 1, Line 1
XML parsing: line 6, character 5, '>' expected


To resolve the error:

SQL Server - Error Msg 9400, Level 16, State 1, Line

We will get this error when we are trying to query XML in SQL Server. 
 
Each and every XML node should have closing node like this <element>....</element>. XML will become invalid when it has backslash "\" instead of forward slash "/"

Below error will be throwed  when input XML doesn't have proper enclosing node :

Msg 9400, Level 16, State 1, Line 1
XML parsing: line 6, character 5, unexpected end of input
 

To resolve the error:


SQL Server - Error Msg 9455, Level 16, State 1, Line

We will get this error when we are trying to query XML in SQL Server. 
 
Each and every XML node should have closing node like this <element>....</element>. XML will become invalid when it has backslash "\" instead of forward slash "/" and below error will be throwed :
 
Msg 9455, Level 16, State 1, Line 1
XML parsing: line 6, character 2, illegal qualified name character
 

To resolve the error :


SSIS - Export XML - XML destination

In this post, I will share with you on how I have exported xml returned from a Stored procedure as xml file in a folder using SSIS.
 
Below is my Stored procedure:

SQL Server XML - Example for adding Namespaces

Recently, my colleague came to me and said he was able to generate XML from relational data.
But he wasn't able to prefix some text before each XML tag.

After seeing his required XML format to be generated, I told him adding namespace will do.

Below is an example for adding multiple namespaces to the XML constructed by the FOR XML query:

--Sample data

DECLARE @StudentInfo TABLE (Id INT, Name VARCHAR(20),Subject VARCHAR(20),Marks INT)
INSERT @StudentInfo SELECT 1,'Sathya','Maths','60'
INSERT @StudentInfo SELECT 2,'Deepak','Maths','80'
--SELECT * FROM @StudentInfo

--Relational data - > XML using FOR XML clause

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>
    <StudentID>2</StudentID>
    <StudentName>Deepak</StudentName>
    <CourseID>2</CourseID>
  </Student>
  <Student>
    <StudentID>3</StudentID>
    <StudentName>sathish</StudentName>
    <CourseID>3</CourseID>
  </Student>
</Studentinfo>




In the above sample XML fragment , 

<Studentinfo>        is the root node
<Student>       is the element node,so in the above XML fragment,we havee 3 element nodes.

 <StudentID>   ,
 <StudentName> ,
 <CourseID> ..  are the attribute nodes

1,sathya,1,.. are the attribute values.

<Student> is the start tag &   </Student> is the end tag of the element node.
Similarly,
<Studentinfo> is the start tag & </Studentinfo> is the end tag of root node.

To put it simple in database terminologies,consider
root node as the database name,element node as the table name,attribute node as column name & attribute values as column values.

<!—Student Information -->  are XML comments denoted by <!-- and --> delimiters

<?xml version = "1.0" encoding = "UTF-16"?> are XML processing instructions  marked by <? and ?> delimiters.

A processing instruction is a means to provide additional metadata to a processing application.


 FOR XML clause:


For getting relational data in the form of XML, FOR XML clause was introduced.



Using FOR XML clause,we can represent relational data in the form of XML in two ways:
 1.)    Attribute Centric
2.)    Element Centric







/*******************************Sample data ***********************************/

Let us create sample datas for all below XML examples:

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


SSIS - Shred data from XML file in folder into columns of a table

This article is about shredding data from XML file in a folder into columns of a table using SQL Server Integration services (SSIS) .

Example :

Step 1 : Drag-drop Data Flow Task on to the Control Flow tab



Step 2 : Double-click on Data Flow Task , We will be directed to the Control Flow tab

Step 3 : In the Control Flow tab  , drag - drop
XML Source , Data Conversion and OLE DB Destination tasks .


Consider I have XML file  named Stud.xml in the path D:\

<StudentInfo>
  <Student>
    <Id>1111</Id>
    <Name>Sathya</Name>
  </Student>
  <Student>
    <Id>1112</Id>
    <Name>Deepak</Name>
  </Student>
</StudentInfo>



Step 4 : Double-click XML Source task and do the settings as shown in below image :

SQL Server XML Bulk load examples

This article is about bulk loading XML file into tables (shredding XML data into columns of table)  :
i) Loading single table
ii)Loading multiple tables (With Foreign key relationship)
iii)Handling identity column insert while loading XML file
iv)Handling table creation before loading XML file

Prerequisites :  (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

Step by step Installation guide for SQLXML 4.0 SP1 - 

i) Example : Loading single table

Step 1 : Create table

USE [AdventureWorks2012]
GO

CREATE TABLE Student (Id INT , Name VARCHAR(100))
 

Step 2 : Open notepad and place the XML and save it as Studxml.xml

<StudentInfo>
  <Student>
    <Id>1111</Id>
    <Name>Sathya</Name>
  </Student>
  <Student>
    <Id>1112</Id>
    <Name>Deepak</Name>
  </Student>
</StudentInfo>


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 click on it





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





 





Generate XML with Same Node Names using FOR XML PATH

Avoid T (space) while generating XML using FOR XML clause

Generate XML - Column names as tag name with column values as text() enclosed within their column name tag

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>

Reading XML file using Powershell

This post deals with reading XML file and then traversing through the nodes of XML and fetching node values using powershell commands.

consider the below xml fragment with file path location

<STUDENTINFO>
<student ID="1" name="Sathya">
</student>
<student ID="2" name="Deepak">
</student>
</STUDENTINFO>


#loading xml from mentioned path location into xml variable
[xml]$Xmlvar = Get-Content D:\myxml.xml 


#fetching values of xml nodes

$firststudent = $Xmlvar.STUDENTINFO.student[0]
$firststudent

$secondstudent = $Xmlvar.STUDENTINFO.student[1]
$secondstudent 
 
 
foreach( $student in $Xmlvar.STUDENTINFO.student)

{

Write-host $student.name
Write-host $student.Id

}  


$Xmlvar.SelectSingleNode("/STUDENTINFO/student")

$Xmlvar.SelectNodes("/STUDENTINFO/student")







SQL Server - XML - Error - "The FOR XML clause is not allowed in a INSERT statement"

When we try to insert into column of XML datatype,the result set from (SELECT * FROM TABLE FOR XML RAW/AUTO/PATH),we will end up with following error:

Msg 6819, Level 16, State 1, Line 1
The FOR XML clause is not allowed in a INSERT statement.

Two methods to overcome the above error :


--Sample data

CREATE TABLE TEST_TABLE(Col1 INT IDENTITY(1,1),Col2 VARCHAR(20))
INSERT TEST_TABLE SELECT 'SQL Server 2000'
INSERT TEST_TABLE SELECT 'SQL Server 2005'
INSERT TEST_TABLE SELECT 'SQL Server 2008'
INSERT TEST_TABLE SELECT 'SQL Server 2008R2'
INSERT TEST_TABLE SELECT 'SQL Server 2012'

 


SELECT * FROM TEST_TABLE
SELECT * FROM TEST_TABLE FOR XML AUTO,TYPE



CREATE TABLE TEST_XML(Column_XML XML)

--let us try to insert
 
INSERT TEST_XML SELECT * FROM TEST_TABLE FOR XML AUTO

Msg 6819, Level 16, State 1, Line 1
The FOR XML clause is not allowed in a INSERT statement.


--again,let us try to insert

INSERT TEST_XML SELECT * FROM TEST_TABLE FOR XML AUTO,TYPE

Msg 6819, Level 16, State 1, Line 1
The FOR XML clause is not allowed in a INSERT statement.




Method 1:

DECLARE @VAR VARCHAR(MAX) = 'SELECT * FROM TEST_TABLE FOR XML AUTO,TYPE'
INSERT TEST_XML EXEC (@Var)


SELECT * FROM TEST_XML

Method 2:

INSERT TEST_XML SELECT(SELECT * FROM TEST_TABLE FOR XML AUTO)


SELECT * FROM TEST_XML



 

SQL Server - Remove XML tags from XML document and combine values of XML nodes into single paragraph

Example -  Remove XML tags from XML document and convert values of XML nodes into single paragraph  


USE AdventureWorks2012

WITH XMLNAMESPACES( 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume' AS  "RES")
UPDATE [AdventureWorks2012].[HumanResources].[JobCandidate]
SET Resume.modify('
  insert(<?xml-stylesheet href="Resume.xsl" type="text/xsl"?>)
  before(/RES:Resume)[1]')
WHERE JobcandidateID  IS NOT NULL;

Try the below query,copy the XML document and paste it into notepad and save it as .xhtml as shown in below image:

SELECT Resume FROM [AdventureWorks2012].[HumanResources].[JobCandidate]



Now open the saved file ,you will notice as shown in below image :


Advertisements