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

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…

Details