remove rows from xml data type

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

remove rows from xml data type

Author

Topic

dionisis
Starting Member

5 Posts

Posted - 2014-07-10 : 07:42:36

Hi guys,

I am trying to remove from the xml datatype field some rows. I have read many guides but unfortunately it looks i need a little push. It would be appreciated if someone can help me. Here is my code:


DECLARE @Xml TABLE (Data XML)
DECLARE @Data XML

set @Data=N'
<resultset>
    <row>
        <column name="PieceNo">1</column>
        <column name="PieceNoEvalUnit">655360</column>
        <column name="PieceIdent">Function check1</column>
        <column name="RequestNo">1</column>
    </row>
    <row>
        <column name="PieceNo">2</column>
        <column name="PieceNoEvalUnit">655362</column>
        <column name="PieceIdent">Function check2</column>
        <column name="RequestNo">1</column>
    </row>
    <row>
        <column name="PieceNo">3</column>
        <column name="PieceNoEvalUnit">655364</column>
        <column name="PieceIdent">Function check3</column>
        <column name="RequestNo">1</column>
    </row>
</resultset>'

INSERT  INTO @Xml( Data)
values(@data)

select 
X.N.value('(column/text())[1]', 'int') as PieceNo,
X.N.value('(column/text())[2]', 'int') as PieceNoEvalUnit,
X.N.value('(column/text())[3]', 'varchar(255)') as PieceIdent,
X.N.value('(column/text())[4]', 'int') as RequestNo
from 
@Xml as T
cross apply t.Data.nodes(N'/resultset/row') as X(N)


--update @xml set data.modify('
--delete //row//column[text()][.PieceNo >= sql:variable("2")]
--')

Let's say for example that i want to delete from xml some rows when PieceNo>=2.

Thank you

dionisis
Starting Member

5 Posts

Posted - 2014-07-10 : 07:53:05

Oups..Sorry..! i just figured out that this topic is for 2012. Please remove it to the SQL 2008 topic

thank you

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-07-10 : 09:17:53

[code]
UPDATE @xml set Data.modify('delete /resultset/row[column/@name = "PieceNo" and column/text() = sql:variable("@iValue")]')
[/code]

sabinWeb MCP

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-07-10 : 09:18:31

[code]

DECLARE @Xml TABLE (Data XML)
DECLARE @Data XML
DECLARE @iValue INT = 2

set @Data=N'
<resultset>
<row>
<column name="PieceNo">1</column>
<column name="PieceNoEvalUnit">655360</column>
<column name="PieceIdent">Function check1</column>
<column name="RequestNo">1</column>
</row>
<row>
<column name="PieceNo">2</column>
<column name="PieceNoEvalUnit">655362</column>
<column name="PieceIdent">Function check2</column>
<column name="RequestNo">1</column>
</row>
<row>
<column name="PieceNo">3</column>
<column name="PieceNoEvalUnit">655364</column>
<column name="PieceIdent">Function check3</column>
<column name="RequestNo">1</column>
</row>
</resultset>'

INSERT INTO @Xml( Data)
values(@data)

select
X.N.value('(column/text())[1]', 'int') as PieceNo,
X.N.value('(column/text())[2]', 'int') as PieceNoEvalUnit,
X.N.value('(column/text())[3]', 'varchar(255)') as PieceIdent,
X.N.value('(column/text())[4]', 'int') as RequestNo
from
@Xml as T
cross apply t.Data.nodes(N'/resultset/row') as X(N)

UPDATE @xml set Data.modify('delete /resultset/row[column/@name = "PieceNo" and column/text() = sql:variable("@iValue")]')

select
X.N.value('(column/text())[1]', 'int') as PieceNo,
X.N.value('(column/text())[2]', 'int') as PieceNoEvalUnit,
X.N.value('(column/text())[3]', 'varchar(255)') as PieceIdent,
X.N.value('(column/text())[4]', 'int') as RequestNo
from
@Xml as T
cross apply t.Data.nodes(N'/resultset/row') as X(N)

/*
select
X.N.value('(column/text())[1]', 'int') as PieceNo,
X.N.value('(column/text())[2]', 'int') as PieceNoEvalUnit,
X.N.value('(column/text())[3]', 'varchar(255)') as PieceIdent,
X.N.value('(column/text())[4]', 'int') as RequestNo
from
@Xml as T
cross apply t.Data.nodes(N'/resultset/row') as X(N)
*/
[/code]

sabinWeb MCP

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-07-10 : 09:28:38

replace update with


UPDATE @xml set Data.modify('delete /resultset/row[column/@name = "PieceNo" and column[1]/text() >= sql:variable("@iValue")]')

sabinWeb MCP

dionisis
Starting Member

5 Posts

Posted - 2014-07-10 : 09:33:39

thank you very much stepson..!!

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-07-10 : 09:35:39

with welcome!

sabinWeb MCP

dionisis
Starting Member

5 Posts

Posted - 2014-07-10 : 11:04:39

..and what if i would like to add a where clause to my query?

select 
X.N.value('(column/text())[1]', 'int') as PieceNo,
X.N.value('(column/text())[2]', 'int') as PieceNoEvalUnit,
X.N.value('(column/text())[3]', 'varchar(255)') as PieceIdent,
X.N.value('(column/text())[4]', 'int') as RequestNo
from 
@tbl as T
cross apply t.tblxml.nodes(N'/resultset/row') X(N)

i tried with this but looks that SQL doesn't like it..

select 
X.N.value('(column/text())[1]', 'int') as PieceNo,
X.N.value('(column/text())[2]', 'int') as PieceNoEvalUnit,
X.N.value('(column/text())[3]', 'varchar(255)') as PieceIdent,
X.N.value('(column/text())[4]', 'int') as RequestNo
from 
@tbl as T
cross apply t.tblxml.nodes(N'/resultset/row') X(N)
where X.N.value('(column/text())[1]')>@lastPieceNo

dionisis
Starting Member

5 Posts

Posted - 2014-07-10 : 11:17:51

it is fine i had to edit my code to


where X.N.value('(column/text())[1]', 'int')>@lastPieceNo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources