CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
KeyboardFriendly
KeyboardFriendly

Reputation: 1798

Sql Server Xml Value method -- Distinct Values for one column

I have the below query I am trying to return distinct value from the second .value method.

Here is what I have tried. I tried adding 'distinct-values(.)' to return only distinct but it is still returning the same results as a normal '.' How can I select distinct values from just one column?

;WITH XMLNAMESPACES (default 'http://www.w3.org/2001/XMLSchema')

SELECT  

              a.value('.', 'NVARCHAR(50)') AS Visitor          
            , b.value('distinct-values(.)', 'NVARCHAR(50)') AS Sender
FROM         XmlTable AS X 

            CROSS APPLY xmlDocument.nodes('Root/Visitors/Visitor') AS aa(a) 
            CROSS APPLY xmlDocument.nodes('Root/Senders/Sender') AS bb(b)

Here is the normal result

enter image description here

Here is whay I am trying to get

enter image description here

Xml Like this 

<upx:Root xmlns:upx="http://www.w3.org/2001/XMLSchema">
  <upx:Visitors>
    <upx:Visitor>Visitor1</upx:Visitor>
    <upx:Visitor>Visitor2</upx:Visitor>
  </upx:Visitors>
  <upx:Senders>
    <upx:Sender>Sender1</upx:Sender>
  </upx:Senders> 
</upx:Root>

Upvotes: 1

Views: 1350

Answers (1)

djangojazz
djangojazz

Reputation: 13242

It is your cross apply with your nodes statement listed twice that is showing this problem. Do what you are doing with the 'nodes' syntax with a 'query' extension instead followed up by a 'value' extension to show what is in the xml directly from extension instead of relying on the nodes with a cross apply. The problem is you are not displaying to the audience where you get that Id from? Are you determining that at run time from the xml itself or joining yet to another table or having another part of the xml not present? What in essence that is happening with the nodes is it is cross applying and saying: "I have two vales in that node heirarchy here they are." Then you are cross applying again a different node and it is returning the same thing twice. You must be careful when using cross apply twice exactly what it is doing. I can show the differentiation but without how I know you are relating back to 1 (are you just hunting for it somehow for the int after visitor?) I don't know how to represent exactly what you are wanting.

EDIT: Okay it is what I thought then. Now my code may be longer than some and I will admit there may be an easier way to do this however I would do three things:

  1. Keep your cross apply with nodes because nodes is useful in that it will repeat rows you need to count on. However I would add an artificial flag for the name you use for the node. Then I would union together two select statements using the nodes.
  2. I would then use a nested select as a from statement and then determine row number with a windowed function based on the flags I just set.
  3. I would then nest that again and then use the very same row number as the Id of the row number and then I would do some syntactic pivoting based on a max(case when) based on the flags I arbitrarily set.

I usually prefer cte's but since your XML namespace has a 'with' beginning and the first cte does as well I forgot how the syntax is to work around that. Nested Selects IMHO can get hairy when there are multiple so I choose CTE's usually but in this case I did a nested select inside of another nested select. I hope this helps:

declare @xml xml = '<upx:Root xmlns:upx="http://www.w3.org/2001/XMLSchema">
  <upx:Visitors>
    <upx:Visitor>Visitor1</upx:Visitor>
    <upx:Visitor>Visitor2</upx:Visitor>
  </upx:Visitors>
  <upx:Senders>
    <upx:Sender>Sender1</upx:Sender>
  </upx:Senders> 
</upx:Root>'
;

declare @Xmltable table ( xmlDocument xml);

insert into @XmlTable values (@xml);

WITH XMLNAMESPACES (default 'http://www.w3.org/2001/XMLSchema')

select
    pos as Id
,   max(case when Listing = 'Visitors' then Value end) as Visitors
,   max(case when Listing = 'Senders' then Value end) as Senders
from 
    (
    select 
        *
    ,   row_number() over(partition by Listing order by Value) as pos
        from 
            (
            SELECT  
                'Visitors' as Listing
            ,   a.value('.', 'NVARCHAR(50)') AS Value
            FROM @XmlTable AS X 
                CROSS APPLY xmlDocument.nodes('Root/Visitors/Visitor') AS aa(a) 
            union
            SELECT  
                'Senders'
            ,   b.value('distinct-values(.)', 'NVARCHAR(50)') AS Sender
            FROM         @XmlTable AS X 
                CROSS APPLY xmlDocument.nodes('Root/Senders/Sender') AS bb(b)
            ) as u
    ) as listing
group by pos

Upvotes: 2

Related Questions