Search

Advertisements

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

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>

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