Tag Archives: xml

Update XML attribute value based on condition with XQUERY

Recently I had one article which shows how to update XML attribute value which doesn’t had any checking/condition while update. One of the reader asked me how can he update value based on condition. Here is the solution for it.

CREATE TABLE Orders
(
ID INT IDENTITY(1,1),
ClientID VARCHAR(6),
TestDetails XML,
OrderDate DATETIME DEFAULT GETDATE()
)
GO

INSERT INTO Orders (ClientID,TestDetails)

SELECT ‘CHEM02′,'<TestDetails><Test Dept=”VOC” Name=”VOC MS Group1” /></TestDetails>’ UNION ALL

SELECT ‘ACCU01′,'<TestDetails><Test Dept=”VOC” Name=”SVOC Full” /></TestDetails>’ UNION ALL

SELECT ‘CBI001′,'<TestDetails><Test Dept=”VOC” Name=”PCB” /></TestDetails>’

Now we will change attribute “Dept”’s value from “VOC” to appropriate value.

-- updating new attribute
UPDATE Orders
SET TestDetails.modify('
replace value of (/TestDetails/Test/@Dept)[1]
with (
if (/TestDetails/Test/@Name="SVOC Full") then
"SVOC"
else
"VOC"
)
')
GO

Let us now execute SELECT statement on Orders table to confirm that whether we have new valie in attribute “Dept” added inside “Test” element or not?

SELECT * FROM Orders
GO

I have few more article written on the subject of XML/XQuery, if you are interested, have a look at following list:

Update XML attribute value with XQUERY in SQL Server (Click Here)
XQuery in SQL Server to insert XML attribute in existing XML element (Click Here)
XQuery in SQL Server to delete XML element (Click Here)
WHERE clause on XML column in SQL Server table (Click Here)
Error Fix: Unable to show XML The following error happened There is an unclosed literal string (Click Here)
For XML PATH ROOT in SQL Server 2008 (Click Here)
Read typed XML in SQL Server 2008 (Click Here)
Update UnTyped XML data column in SQL Server 2008/2005 (Click Here)
Return comma separated value with For XML Path in SQL Server 2008/2005 (Click Here)
Read XML node on same level inSQL Server 2008/2005 (Click Here)
Load Relational XML data in SQL Server 2005 (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Update XML attribute value with XQUERY in SQL Server

Recently I have written article on Inserting attribute in existing XML Elements and Delete XML element. Today I am going to show how to update existing attribute value in XML elements.

CREATE TABLE Orders
(
ID INT IDENTITY(1,1),
ClientID VARCHAR(6),
TestDetails XML,
OrderDate DATETIME DEFAULT GETDATE()
)
GO

INSERT INTO Orders (ClientID,TestDetails)

SELECT ‘CHEM02′,'<TestDetails><Test Dept=”VOC” Name=”VOC MS Group1” /></TestDetails>’ UNION ALL

SELECT ‘ACCU01′,'<TestDetails><Test Dept=”VOC” Name=”SVOC Full” /></TestDetails>’ UNION ALL

SELECT ‘CBI001′,'<TestDetails><Test Dept=”VOC” Name=”PCB” /></TestDetails>’

While making an initial entry of DEPT, I have provided “VOC” but now I felt I was wrong and I have to put “SVOC” instead of “VOC” and in this case I have to update existing value of “Dept” attribute. Here is the script which I would use to update attribute value without any checking/condition.

DECLARE @Dept VARCHAR(10)
SELECT @Dept = 'SVOC'

-- updating attribute
UPDATE Orders
SET TestDetails.modify('
replace value of (/TestDetails/Test/@Dept)[1]
with sql:variable("@Dept")
')
GO

Let us now execute SELECT statement on Orders table to confirm that whether we have new valie in attribute “Dept” added inside “Test” element or not?

SELECT * FROM Orders
GO

I have few more article written on the subject of XML/XQuery, if you are interested, have a look at following list:

XQuery in SQL Server to insert XML attribute in existing XML element (Click Here)
XQuery in SQL Server to delete XML element (Click Here)
WHERE clause on XML column in SQL Server table (Click Here)
Error Fix: Unable to show XML The following error happened There is an unclosed literal string (Click Here)
For XML PATH ROOT in SQL Server 2008 (Click Here)
Read typed XML in SQL Server 2008 (Click Here)
Update UnTyped XML data column in SQL Server 2008/2005 (Click Here)
Return comma separated value with For XML Path in SQL Server 2008/2005 (Click Here)
Read XML node on same level inSQL Server 2008/2005 (Click Here)
Load Relational XML data in SQL Server 2005 (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

XQuery in SQL Server to insert XML attribute in existing XML element

Use of XML is being popular in SQL Server and it comes handy in many situation as I have explained in many of my past Xquery/XML related article. Today I come up with one more example which shows usage of XQuery to insert element from XML data.

CREATE TABLE Orders
(
ID INT IDENTITY(1,1),
ClientID VARCHAR(6),
TestDetails XML,
OrderDate DATETIME DEFAULT GETDATE()
)
GO

INSERT INTO Orders (ClientID,TestDetails)

SELECT ‘CHEM02′,'<TestDetails><Test Name=”VOC MS Group1” /></TestDetails>’ UNION ALL

SELECT ‘ACCU01′,'<TestDetails><Test Name=”SVOC Full” /></TestDetails>’ UNION ALL

SELECT ‘CBI001′,'<TestDetails><Test Name=”PCB” /></TestDetails>’

Each test (i.e.: Voc MS Group1, SVOC Full) has one department which is not mentioned as an attribute in TestDetails node. Let us insert “Dept” attribute.

DECLARE @Dept VARCHAR(10)
SELECT @Dept = 'Volatile'

-- Adding new attribute
UPDATE Orders
SET TestDetails.modify('
insert attribute Dept {sql:variable("@Dept")} as first into
(/TestDetails/Test)[1]
')
GO

Let us now execute SELECT statement on Orders table to confirm that whether we have new attribute “Dept” added in “Test” element or not?

SELECT * FROM Orders
GO

I have few more article written on the subject of XML/XQuery, if you are interested, have a look at following list:

XQuery in SQL Server to delete XML element (Click Here)
WHERE clause on XML column in SQL Server table (Click Here)
Error Fix: Unable to show XML The following error happened There is an unclosed literal string (Click Here)
For XML PATH ROOT in SQL Server 2008 (Click Here)
Read typed XML in SQL Server 2008 (Click Here)
Update UnTyped XML data column in SQL Server 2008/2005 (Click Here)
Return comma separated value with For XML Path in SQL Server 2008/2005 (Click Here)
Read XML node on same level inSQL Server 2008/2005 (Click Here)
Load Relational XML data in SQL Server 2005 (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

XQuery in SQL Server to delete XML element

CREATE TABLE Orders
(
ID INT IDENTITY(1,1),
ClientID VARCHAR(6),
TestDetails XML,
OrderDate DATETIME DEFAULT GETDATE()
)
GO

INSERT INTO Orders (ClientID,TestDetails)
SELECT ‘CHEM02′,'<TestDetails><Test Name=”VOC MS Group1” /><Test Name=”Pesticide Group1” /></TestDetails>’ UNION ALL
SELECT ‘ACCU01′,'<TestDetails><Test Name=”SVOC Full” /><Test Name=”Pesticide Group1” /></TestDetails>’ UNION ALL
SELECT ‘CBI001′,'<TestDetails><Test Name=”PCB” /><Test Name=”Metals” /></TestDetails>’
GO

We can see that we have “TestDetails” as a root element in above given XML along with child elements “Test”. Suppose we have requirement to delete all elements which has test name “Pesticide Group1”, how can we do it with the help of XQuery?

Here is the answer:

--Update with XQuery to Delete element
UPDATE Orders SET
TestDetails.modify ('
delete
/TestDetails/Test[@Name cast as xs:string ? = "Pesticide Group1" cast as xs:string ?]')
GO

-- confirm result by querying Orders Table
SELECT * FROM Orders

I have few more article written on the subject of XML/XQuery, if you are interested, have a look at following list:

WHERE clause on XML column in SQL Server table (Click Here)
Error Fix: Unable to show XML The following error happened There is an unclosed literal string (Click Here)
For XML PATH ROOT in SQL Server 2008 (Click Here)
Read typed XML in SQL Server 2008 (Click Here)
Update UnTyped XML data column in SQL Server 2008/2005 (Click Here)
Return comma separated value with For XML Path in SQL Server 2008/2005 (Click Here)
Read XML node on same level inSQL Server 2008/2005 (Click Here)
Load Relational XML data in SQL Server 2005 (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

WHERE clause on XML column in SQL Server table

Xquery is one of the wonderful feature I appreciate most in SQL Server. Due to lack of knowledge, people doesn’t tend to use it. I always prefer XML in case of I have to pass multiple values in one parameter of stored procedure. It is even useful many time to store XML value along with our relational data in SQL Server table. I, personally, prefer it while capturing and storing performance related data.

Recently I have received on question in my facebook page where one member has shown me one table structure and ask how he can use XML data in WHERE clause of SELECT statement.

It is very easy to use “Exist” method to filter XML from the WHERE clause. Let us see one example:

CREATE TABLE Orders
(
ID INT IDENTITY(1,1),
ClientID VARCHAR(6),
TestDetails XML,
OrderDate DATETIME DEFAULT GETDATE()
)
GO

INSERT INTO Orders (ClientID,TestDetails)|
SELECT ‘CHEM02′,'<TestDetails><Test Name=”VOC MS Group1” /><Test Name=”Pesticide Group1” /></TestDetails>’ UNION ALL
SELECT ‘ACCU01′,'<TestDetails><Test Name=”SVOC Full” /><Test Name=”Pesticide Group1” /></TestDetails>’ UNION ALL
SELECT ‘CBI001′,'<TestDetails><Test Name=”PCB” /><Test Name=”Metals” /></TestDetails>’
GO

Now, we have one table ready with one XML column. We want only those records whose XML data is having value “Pesticide Group1”. We can create WHERE clause for that SELECT query like this:

SELECT
*
FROM
Orders
WHERE
TestDetails.exist('(/TestDetails/Test[@Name=''Pesticide Group1''])') = 1
GO

I have few more article written on the subject of XML, if you are interested, have a look at following list:

Error Fix: Unable to show XML The following error happened There is an unclosed literal string (Click Here)
For XML PATH ROOT in SQL Server 2008 (Click Here)
Read typed XML in SQL Server 2008 (Click Here)
Update UnTyped XML data column in SQL Server 2008/2005 (Click Here)
Return comma separated value with For XML Path in SQL Server 2008/2005 (Click Here)
Read XML node on same level inSQL Server 2008/2005 (Click Here)
Load Relational XML data in SQL Server 2005 (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Error Fix: Unable to show XML The following error happened There is an unclosed literal string

Recently I was working with huge data of XML in SQL Server. One of the row having XML string with approx 98000 rows inside. If I try to open that XML file by clicking on XML value return by my SELECT query, I used to get following error:

Unable to show XML. The following error happened:
There is an unclosed literal string. Line 1, position 2097154.

One solution is to increase the number of characters retrieved from the server for XML data. To change this setting, on the Tools menu, click Options.

Here is the screen capture of error:

This error comes because of limitation set by default of 2MB for XML data. My data for that XML field was having approx 18MB. To open this big file, I have to change the SSMS configuration from Tools | Options.

You can see that “Results to Grid” has option to retrieve maximum characters. For “Non XML Data:”, it is 65535 and for XML data, it is 2MB. My data was around 18MB so obviously I can’t get it open in SSMS. I have changed (temporary) it to “Unlimited” for a while, clicked on “OK” button. Close my current query window and execute query again. I got my XML field in SSMS and when I have clicked on that field, I got my XML file open.
If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

For XML PATH ROOT in SQL Server 2008

Now and Then I used to observe that when it comes to XML, .NET and SQL Developer start fearing. This is not because of It is VERY DIFFICULT but because of unawareness. Today I am going to show you the use of “FOR XML PATH” clause in SQL Server.

Suppose we have ID, FirstName and LastName columns in one of our SQL Server table and we want output something like this:

Employee>

Employee>

Employees>

Let me give you TSQL to generate table in SQL Server and insert records in that.

Create Table EmployeeData

(

ID INT Identity(1,1),

FirstName varchar(10),

LastName varchar(10)

)

insert into EmployeeData

select ‘Ritesh’,‘Shah’ UNION ALL

select ‘Rajan’,‘Jain’

So now, here is the use of “FOR XML PATH ROOT” clause which will make our life easier in this kind of situation.

select ID as “ID/@value”

, FirstName as “FirstName/@value”

, LastName as “LastName/@value”

from (

Select * from EmployeeData

) as t

for xml path(‘Employee’), root(‘Employees’);

Isn’t it easy to use?

BTW, below given are some links which will redirect you to my some of the past articles on XML subject.

Read Typed XML in SQL Server

Load relational XML data

Read XML node on same level

Return comma separated value with “For XML Path”

update UnTyped XML data column

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Update UnTyped XML data column in SQL Server 2008/2005

Now a day, XML is booming, it is welcomed from every platform so why Microsoft and especially SQL Server sit back? After SQL Server 2005, Microsoft have provided so many functionality for make developer’s life easy who are using XML. Today, I would like you to show how you can update un-typed XML data from within well known UPDATE T-SQL statement.

–create table for testing purpose with XML column

–in SQL Server 2005 or in 2008

create table xmlTest

(

ColumnXML xml

)

–insert data in XML column

insert into xmlTest

select

‘

  A1000
  Ritesh Shah
  HP Notebook

‘

–check the output

select * from xmlTest

–update value (un-typed) in product tag

DECLARE @Val varchar(50)

SELECT @Val = ‘Dell Inspiron’

UPDATE xmlTest

SET ColumnXML.modify(‘replace value of (/Orders/Product/text())[1] with sql:variable(“@Val”)’)

–confirm changes

select * from xmlTest

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Read XML node on same level inSQL Server 2008/2005

I have written one article few days back for loading relational data in SQL Server from XML file. I got very good response from that article so I am tempted write another one on XML. Suppose you have different occurrences of data within same level of XML file, how would you query or populate in SQL Server? Let us have a look at it.

–create one variable with XML data

–you can have .XML file saved on your disk

–and can use the same query

DECLARE @xData xml;

SET @xData = ‘

ch001

Ritesh

Shah

MIS

Network

DBA

ch002

Rushik

Shah

Programmer

Developer

‘

–suppose employee work in more than one department

–how would you list employee with all concern department?

–ofcourse with below give query ;)

SELECT outr.ID,outr.FName, outrmost.y.value(‘text()[1]’,‘varchar(200)’) AS imageUrl

FROM

(

SELECT

Inr.i.value(‘id[1]’,‘varchar(20)’) AS ID,

Inr.i.value(‘FName[1]’,‘varchar(20)’) + ‘ ‘ + Inr.i.value(‘LName[1]’,‘varchar(20)’) AS FName,

Inr.i.query(‘.’) AS employee_desc

FROM @xData.nodes(‘/chem/employee’) AS Inr(i)

) AS outr

CROSS APPLY outr.employee_desc.nodes(‘//dept’) AS outrmost(y)

–or you can just list of department based on ID

SELECT outrmost.y.value(‘text()[1]’,‘varchar(200)’) AS imageUrl

FROM

(

SELECT

Inr.i.value(‘id[1]’,‘varchar(20)’) AS ID,

–Inr.i.value(‘FName[1]’,’varchar(20)’) + ‘ ‘ + Inr.i.value(‘LName[1]’,’varchar(20)’) AS FName,

Inr.i.query(‘.’) AS employee_desc

FROM @xData.nodes(‘/chem/employee’) AS Inr(i)

) AS outr

CROSS APPLY outr.employee_desc.nodes(‘//dept’) AS outrmost(y)

where outr.ID=‘ch001’

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Load Relational XML data in SQL Server 2005

When question comes to play with XML, even seasoned programmer try to get away from it, not because it is difficult task but lack of knowledge. I promise, loading XML data into SQL Server 2005 as a relational data, is not going to be as difficult as you are thinking.

Let me show you one example which will fill two relational tables in SQL Server from XML data. Let us first create one XML file at C:\vendor.XML with following data in it.

<c>

<venID>101venID>

<first_name>Jamesfirst_name>

<last_name>Butlerlast_name>

<po>

<venID>101venID>

<po_date>2008-04-01T00:00:00po_date>

<price>400.00price>

po>

<po>

<venID>101venID>

<po_date>2008-05-01T00:00:00po_date>

<price>299.0000price>

po>

<po>

<venID>101venID>

<po_date>2009-04-01T00:00:00po_date>

<price>600.0000price>

po>

vendor>

Now, create two tables to feed vendor details and purchase order detail, than we will load XML file into both tables.

–create table to hold Vendor information

CREATE TABLE vendor (

venid INT NOT NULL

, first_name VARCHAR(50)

, last_name VARCHAR(50)

)

–create table for holding Purchase order information.

CREATE TABLE PurcharOrder (

poID INT NOT NULL

, venid INT NOT NULL

, po_date DATETIME

, price MONEY

)

–hold xml from file

DECLARE @x XML

SET @x= (

SELECT xm.Col1 FROM OPENROWSET(BULK ‘c:\vendor.xml’,SINGLE_BLOB) AS xm(Col1)

)

— show file contents

select @x

INSERT INTO vendor

SELECT

ven.value(‘./venID[1]’, ‘INT’) as vid

, ven.value(‘./first_name[1]’, ‘VARCHAR(50)’) as first_name

, ven.value(‘./last_name[1]’, ‘VARCHAR(50)’) as last_name

–, C.query(‘.’)

FROM @x.nodes(‘/vendor/c’) tab(ven)

INSERT INTO PurcharOrder

SELECT

ven.value(‘./poid[1]’, ‘INT’) as POID

, ven.value(‘./venID[1]’, ‘INT’) as VenID

, ven.value(‘./po_date[1]’, ‘DATETIME’) as po_date

, ven.value(‘./price[1]’, ‘MONEY’) as price

–,ven.query(‘.’)

FROM @x.nodes(‘/vendor/c/po’) tab(ven)

–check both table, whether data comes or not.

select * FROM Vendor

Select * from PurcharOrder

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Ritesh Shah (Extreme-Advice.Com)

Technology Blog

Tag Archives: xml

Update XML attribute value based on condition with XQUERY

Update XML attribute value with XQUERY in SQL Server

XQuery in SQL Server to insert XML attribute in existing XML element

XQuery in SQL Server to delete XML element

WHERE clause on XML column in SQL Server table

Error Fix: Unable to show XML The following error happened There is an unclosed literal string

For XML PATH ROOT in SQL Server 2008

Update UnTyped XML data column in SQL Server 2008/2005

Read XML node on same level inSQL Server 2008/2005

Load Relational XML data in SQL Server 2005