-1

My source is in SQL server; my target is in Snowflake. The target table was created by extracting the XML column only from the source. I need to compare the first 20 rows in source and target to see if the data matches. I need the 20 rows to be in the same order. I queried the source in SQL Server with this:

SELECT TOP 20
COLUMN_XML
FROM TABLE_NAME
ORDER BY COLUMN_XML;

I received this error message:

The XML data type cannot be compared or sorted, except when using the IS NULL operator.

I'm wondering if anyone can tell me if I can query the top 20 rows in the XML column in source and target in a way so they are in the same order?

2
  • Although I could query a second column in the source, and order by that, I can't query the same additional column in the target because the target contains only the XML column and no other columns.
    – Jimbo
    Commented Jan 3, 2023 at 19:42
  • When I posted my question, I used shift return to break my SQL query into four lines rather than one. But when I posted it, it changed that back to one line. How do I ensure my code will be displayed in multiple lines when it is posted?
    – Jimbo
    Commented Jan 4, 2023 at 16:44

2 Answers 2

4

overall

This seems like a really bad design choice. It would be wiser to parse out the XML into proper normalized tables to compare. XML is just one of those redheaded data types that has a lot more restrictions on it than others.

You can use the below examples to do what you want, but it's probably going to perform absolutely terrible.

DECLARE 
    @t table (x xml);

INSERT 
    @t
(
    x
)
VALUES
(
    N'<x>x</x>'
);    

SELECT TOP (1) 
    t.x 
FROM @t AS t
WHERE CONVERT(nvarchar(MAX), t.x) IN 
    (
        SELECT
            CONVERT(nvarchar(MAX), t2.x)
        FROM @t AS t2
    )
ORDER BY 
    CONVERT(nvarchar(MAX), t.x);

I'd seriously re-think this approach if I were you.

0
2

I'm wondering if anyone can tell me if I can query the top 20 rows in the XML column

Highly unlikely because SQLServer is saying "No".

XML is just a big block of text and trying to sort by big blocks of text is likely to perform [very] badly.

XML is a composite data type.
It's made up of much smaller bits and it probably makes sense to sort by one [or more] of those "bits", rather than the whole thing. As Erik says, you're probably better off normalising [at least some of] this into more sensible, Relational structures.

XML can also be a big "woolly" in its definition and/or implementation.
Attributes are officially unordered lists of key-value pairs so, while XML would consider these two as equivalent, sorting them as text would not!

<element1 attr1="value1" attr2="value2"/>
<element1 attr2="value2" attr1="value1"/>
1
  • Thanks Phil. This is helpful.
    – Jimbo
    Commented Jan 4, 2023 at 16:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.