Styjun

What exactly is the 'online' in OLAP and OLTP?

Suggested order for Amazon Prime Doctor Who series

How is hair tissue mineral analysis performed?

Can humans ever directly see a few photons at a time? Can a human see a single photon?

Why do some professors with PhDs leave their professorships to teach high school?

How does a blind passenger not die, if driver becomes unconscious

Unusual mail headers, evidence of an attempted attack. Have I been pwned?

What happened to Steve's Shield in Iron Man 2?

Concurrent normals conjecture

Non-flat partitions of a set

What does the hyphen "-" mean in "tar xzf -"?

What does it mean to "control target player"?

Heavily limited premature compiler translates text into excecutable python code

Are all Ringwraiths called Nazgûl in LotR?

How large would a mega structure have to be to host 1 billion people indefinitely?

Can there be an UN resolution to remove a country from the UNSC?

Cut the gold chain

How to make clear to people I don't want to answer their "Where are you from?" question?

Can White Castle?

Is there a term for the belief that "if it's legal, it's moral"?

Can Ogre clerics use Purify Food and Drink on humanoid characters?

How do I professionally let my manager know I'll quit over smoking in the office?

Is it illegal to withhold someone's passport and green card in California?

Should developer taking test phones home or put in office?

select query to remove nodes from xml column

Insert into … values ( SELECT … FROM … )Select top 10 records for each categorySQL exclude a column using SELECT * [except columnA] FROM tableA?How do I UPDATE from a SELECT in SQL Server?What are the options for storing hierarchical data in a relational database?How to query the xml in SQL XML on a SQL Server 2008 databaseQuery optimization in an xml columnSQL select only rows with max value on a columnselect xml rows without column header in SQL Server FOR XMLUpdate XML column with nodes from another XML on SQL Server

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

3

I have a table with an XML column that contains 2 nodes that have large base64 strings (images). When I query the database, I want to remove these 2 nodes from the xml returned to the client. I cannot change the schema of the table (i.e. I cannot split the data in the column). How can I remove the 2 nodes from the xml column using the select statement? (The nodes to remove both contain the text "Image" in their name). Upto 1000 records can be returned in any single query.

Currently, my query basically looks like this:

select top 1000 [MyXmlData] from [MyTable]

The MyXmlData column contains xml that looks something like this:

<MyXml>
 <LotsOfNodes></LotsOfNodes>
 ...
 <ANode>
 ...
 <MyImage1></MyImage1> <!-- remove this from returned xml -->
 <MyImage2></MyImage2> <!-- remove this from returned xml -->
 ...
 </ANode>
 ...
 <LotsOfNodes></LotsOfNodes>
 ...
</MyXml>

I am using SQL Server 2008.

sql xpath

share|improve this question

edited Mar 25 at 8:32

Cody Gray♦

198k37396481

asked May 31 '11 at 12:28

jimaspjimasp

682623

add a comment | 

3

I have a table with an XML column that contains 2 nodes that have large base64 strings (images). When I query the database, I want to remove these 2 nodes from the xml returned to the client. I cannot change the schema of the table (i.e. I cannot split the data in the column). How can I remove the 2 nodes from the xml column using the select statement? (The nodes to remove both contain the text "Image" in their name). Upto 1000 records can be returned in any single query.

Currently, my query basically looks like this:

select top 1000 [MyXmlData] from [MyTable]

The MyXmlData column contains xml that looks something like this:

<MyXml>
 <LotsOfNodes></LotsOfNodes>
 ...
 <ANode>
 ...
 <MyImage1></MyImage1> <!-- remove this from returned xml -->
 <MyImage2></MyImage2> <!-- remove this from returned xml -->
 ...
 </ANode>
 ...
 <LotsOfNodes></LotsOfNodes>
 ...
</MyXml>

I am using SQL Server 2008.

sql xpath

share|improve this question

edited Mar 25 at 8:32

Cody Gray♦

198k37396481

asked May 31 '11 at 12:28

jimaspjimasp

682623

add a comment | 

I have a table with an XML column that contains 2 nodes that have large base64 strings (images). When I query the database, I want to remove these 2 nodes from the xml returned to the client. I cannot change the schema of the table (i.e. I cannot split the data in the column). How can I remove the 2 nodes from the xml column using the select statement? (The nodes to remove both contain the text "Image" in their name). Upto 1000 records can be returned in any single query.

Currently, my query basically looks like this:

select top 1000 [MyXmlData] from [MyTable]

The MyXmlData column contains xml that looks something like this:

<MyXml>
 <LotsOfNodes></LotsOfNodes>
 ...
 <ANode>
 ...
 <MyImage1></MyImage1> <!-- remove this from returned xml -->
 <MyImage2></MyImage2> <!-- remove this from returned xml -->
 ...
 </ANode>
 ...
 <LotsOfNodes></LotsOfNodes>
 ...
</MyXml>

I am using SQL Server 2008.

sql xpath

share|improve this question

edited Mar 25 at 8:32

Cody Gray♦

198k37396481

asked May 31 '11 at 12:28

jimaspjimasp

682623

select top 1000 [MyXmlData] from [MyTable]

<MyXml>
 <LotsOfNodes></LotsOfNodes>
 ...
 <ANode>
 ...
 <MyImage1></MyImage1> <!-- remove this from returned xml -->
 <MyImage2></MyImage2> <!-- remove this from returned xml -->
 ...
 </ANode>
 ...
 <LotsOfNodes></LotsOfNodes>
 ...
</MyXml>

share|improve this question

edited Mar 25 at 8:32

Cody Gray♦

198k37396481

asked May 31 '11 at 12:28

jimaspjimasp

682623

share|improve this question

edited Mar 25 at 8:32

Cody Gray♦

198k37396481

asked May 31 '11 at 12:28

jimaspjimasp

682623

edited Mar 25 at 8:32

Cody Gray♦

198k37396481

edited Mar 25 at 8:32

Cody Gray♦

198k37396481

asked May 31 '11 at 12:28

jimaspjimasp

682623

asked May 31 '11 at 12:28

jimaspjimasp

682623

1 Answer
1

active

oldest

votes

3

This is tested on SQL Server

You can store your query result to a temp table or table variable and use modify() to delete the Image nodes. Use contains() and local-name() to figure out if a node should be deleted or not.

declare @T table(XmlData xml)

insert into @T values
('<MyXml>
 <LotsOfNodes></LotsOfNodes>
 <ANode>
 <MyImage1></MyImage1> <!-- remove this from returned xml -->
 <MyImage2></MyImage2> <!-- remove this from returned xml -->
 </ANode>
 <LotsOfNodes></LotsOfNodes>
 </MyXml>')

update @T set
 XmlData.modify('delete //*[contains(local-name(.), "Image")]')

select *
from @T

Result:

<MyXml>
 <LotsOfNodes />
 <ANode>
 <!-- remove this from returned xml -->
 <!-- remove this from returned xml -->
 </ANode>
 <LotsOfNodes />
</MyXml>

share|improve this answer

answered May 31 '11 at 13:08

Mikael ErikssonMikael Eriksson

118k19162234

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f6187570%2fselect-query-to-remove-nodes-from-xml-column%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

3

This is tested on SQL Server

You can store your query result to a temp table or table variable and use modify() to delete the Image nodes. Use contains() and local-name() to figure out if a node should be deleted or not.

declare @T table(XmlData xml)

insert into @T values
('<MyXml>
 <LotsOfNodes></LotsOfNodes>
 <ANode>
 <MyImage1></MyImage1> <!-- remove this from returned xml -->
 <MyImage2></MyImage2> <!-- remove this from returned xml -->
 </ANode>
 <LotsOfNodes></LotsOfNodes>
 </MyXml>')

update @T set
 XmlData.modify('delete //*[contains(local-name(.), "Image")]')

select *
from @T

Result:

<MyXml>
 <LotsOfNodes />
 <ANode>
 <!-- remove this from returned xml -->
 <!-- remove this from returned xml -->
 </ANode>
 <LotsOfNodes />
</MyXml>

share|improve this answer

answered May 31 '11 at 13:08

Mikael ErikssonMikael Eriksson

118k19162234

add a comment | 

3

This is tested on SQL Server

You can store your query result to a temp table or table variable and use modify() to delete the Image nodes. Use contains() and local-name() to figure out if a node should be deleted or not.

declare @T table(XmlData xml)

insert into @T values
('<MyXml>
 <LotsOfNodes></LotsOfNodes>
 <ANode>
 <MyImage1></MyImage1> <!-- remove this from returned xml -->
 <MyImage2></MyImage2> <!-- remove this from returned xml -->
 </ANode>
 <LotsOfNodes></LotsOfNodes>
 </MyXml>')

update @T set
 XmlData.modify('delete //*[contains(local-name(.), "Image")]')

select *
from @T

Result:

<MyXml>
 <LotsOfNodes />
 <ANode>
 <!-- remove this from returned xml -->
 <!-- remove this from returned xml -->
 </ANode>
 <LotsOfNodes />
</MyXml>

share|improve this answer

answered May 31 '11 at 13:08

Mikael ErikssonMikael Eriksson

118k19162234

add a comment | 

This is tested on SQL Server

You can store your query result to a temp table or table variable and use modify() to delete the Image nodes. Use contains() and local-name() to figure out if a node should be deleted or not.

declare @T table(XmlData xml)

insert into @T values
('<MyXml>
 <LotsOfNodes></LotsOfNodes>
 <ANode>
 <MyImage1></MyImage1> <!-- remove this from returned xml -->
 <MyImage2></MyImage2> <!-- remove this from returned xml -->
 </ANode>
 <LotsOfNodes></LotsOfNodes>
 </MyXml>')

update @T set
 XmlData.modify('delete //*[contains(local-name(.), "Image")]')

select *
from @T

Result:

<MyXml>
 <LotsOfNodes />
 <ANode>
 <!-- remove this from returned xml -->
 <!-- remove this from returned xml -->
 </ANode>
 <LotsOfNodes />
</MyXml>

share|improve this answer

answered May 31 '11 at 13:08

Mikael ErikssonMikael Eriksson

118k19162234

declare @T table(XmlData xml)

insert into @T values
('<MyXml>
 <LotsOfNodes></LotsOfNodes>
 <ANode>
 <MyImage1></MyImage1> <!-- remove this from returned xml -->
 <MyImage2></MyImage2> <!-- remove this from returned xml -->
 </ANode>
 <LotsOfNodes></LotsOfNodes>
 </MyXml>')

update @T set
 XmlData.modify('delete //*[contains(local-name(.), "Image")]')

select *
from @T

<MyXml>
 <LotsOfNodes />
 <ANode>
 <!-- remove this from returned xml -->
 <!-- remove this from returned xml -->
 </ANode>
 <LotsOfNodes />
</MyXml>

share|improve this answer

answered May 31 '11 at 13:08

Mikael ErikssonMikael Eriksson

118k19162234

answered May 31 '11 at 13:08

Mikael ErikssonMikael Eriksson

118k19162234

answered May 31 '11 at 13:08

Mikael ErikssonMikael Eriksson

118k19162234