comparing xml column

Question

comparing xml column

astrid 69000

Hall of Fame

Points: 3208
More actions
April 19, 2016 at 7:58 am

#315718

hi,
I have a table that contains one column that is has an xml
And it has come to my attention that maybe not all the xmls are the same. Meaning they have different nodes.
How can I check it without going one by one?
Thanks
Astrid

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Astrid

Cast it as varchar, so that you can do comparisons. Please post some DDL and sample data if you need more detailed help than that.

John

astrid 69000 Hall of Fame Points: 3208 More actions · Answer 2

Hi,

I have attached an excel with a sample of the data.

I don't know what you mean by converting to varchar, inside of each nod there are different values. so we are not comparing one string to the other.

I am just trying to understand if the nodes are the same.

thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

This isn't something I've done myself before. Try the nodes method - it may give you what you're looking for.

John

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

astrid 69000 (4/19/2016)
Hi,
I have attached an excel with a sample of the data.
I don't know what you mean by converting to varchar, inside of each nod there are different values. so we are not comparing one string to the other.
I am just trying to understand if the nodes are the same.
thanks

If you want someone to help you in detail, with working code, please provide sample DDL, data and expected results as per the link in my signature.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Bill Talada SSChampion Points: 11954 More actions · Answer 5

So you want to compare the tree structure and not the values, correct?

I've used this dbo.XmlTable function to do it. Get the function from here:

http://beyondrelational.com/modules/2/blogs/28/posts/10495/xquery-lab-58-select-from-xml.aspx

And here is some simple code to get you started:

SET @cars =

'<?xml version="1.0" encoding="UTF-8"?>

<Cars>

<Car id="1234">

<Make>Volkswagen</Make>

<Model>Eurovan</Model>

<Year>2003</Year>

<Color>White</Color>

<Color>Blue</Color>

</Car>

<Car id="5678">

<Make>Honda</Make>

<Model>CRV</Model>

<Year>2009</Year>

<Color>Black</Color>

<Mileage>35,600</Mileage>

</Car>

</Cars>';

-- list all elements and attributes

SELECT xpath, value

FROM dbo.xmlTable(@cars)

WHERE value is not null

ORDER BY xpath

;

-- using nodes to get all rows

SELECT t.value('Make[1]','NVARCHAR(MAX)')+' '+ t.value('Model[1]','NVARCHAR(MAX)') AS CarType

from @cars.nodes('/Cars/Car') x(t)

astrid 69000 Hall of Fame Points: 3208 More actions · Answer 6

thanks, but I don't understand what you are trying to do.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 7

astrid 69000 (4/19/2016)
thanks, but I don't understand what you are trying to do.

Did you run the code provided?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

astrid 69000 Hall of Fame Points: 3208 More actions · Answer 8

The code that Bill provided yes? and it it didn't work.

I am reading the link he provided

and trying to work with that.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 9

astrid 69000 (4/19/2016)
The code that Bill provided yes? and it it didn't work.
I am reading the link he provided
and trying to work with that.

Saying that something "doesn't work" in the world of IT development really isn't helpful.

The link he provided gives a function which his example invokes.

He was suggesting that you take a look at that function too.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

astrid 69000 Hall of Fame Points: 3208 More actions · Answer 10

Thanks so much.

I read and read and I understood. and it worked :-P:-P

Here is my code

declare @x xml

DECLARE @site_value INT;

SET @site_value = 0;

WHILE @site_value <= 1000

BEGIN

select @x = (select myxml from myxl where id = @site_value)

SET @site_value = @site_value + 1;

insert into mytablexml

SELECT FullPath FROM dbo.XMLTable(@x);

end;

Phil Parkin SSC Guru Points: 246960 More actions · Answer 11

astrid 69000 (4/20/2016)
Thanks so much.
I read and read and I understood. and it worked :-P:-P
Here is my code
declare @x xml
DECLARE @site_value INT;
SET @site_value = 0;
WHILE @site_value <= 1000
BEGIN
select @x = (select myxml from myxl where id = @site_value)
SET @site_value = @site_value + 1;
insert into mytablexml
SELECT FullPath FROM dbo.XMLTable(@x);
end;

Excellent, well done.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin