How to use condition in XQuery?

Question

I have sample xml data with address. It works well but there's one problem. I use "/" to concatenate house number and flat number but if there's no flat number I receive something like that: 1 /.

Here's my example:

declare @xml as xml = '<root>
                    <Row>
                            <proceeding>
                                    <signatures>V GU 86/18</signatures>
                                    <signatures>V GUp 9/19</signatures>
                                    <signatures>V GUp 8/19</signatures>
                            </proceeding>
                            <entity>
                                    <info>
                                            <cleaned_name>Kate Smith</cleaned_name>
                                    </info>
                                    <address>
                                            <town>London </town>
                                            <house_number>1 </house_number>
                                            <flat_number>1</flat_number>
                                            <street>Downing Street</street>
                                            <zip_code>00-001</zip_code>
                                    </address>
                                    <address>
                                            <town>Yorkshire </town>
                                            <house_number>1 </house_number>
                                            <flat_number>1</flat_number>
                                            <street>Morning Street</street>
                                            <zip_code>00-999</zip_code>
                                    </address>
                            </entity>
                            <entity>
                                    <info>
                                            <cleaned_name>John Smith</cleaned_name>
                                    </info>
                                    <address>
                                            <town>Washington </town>
                                            <house_number>1</house_number>
                                            <flat_number>1</flat_number>
                                            <street>Pennsylvania Avenue</street>
                                            <zip_code>00-001</zip_code>
                                    </address>
                                    <address>
                                            <town>Washington </town>
                                            <house_number>1</house_number>
                                            <flat_number></flat_number>
                                            <street>Pennsylvania Avenue</street>
                                            <zip_code>00-001</zip_code>
                                    </address>
                            </entity>
                    </Row>
            </root>'


            select 
             isnull(STUFF(a.x.query('for $s in  entity/info/cleaned_name return <x>{concat(",",$s)}</x>').value('.','varchar(max)'),1,1,''),'') as 'Nazwa podmiotu' 
            ,isnull(STUFF(a.x.query('for $s in proceeding/signatures return <x>{concat(",",$s)}</x>').value('.','varchar(max)'),1,1,''),'') as 'Sygnatura'
            ,isnull(STUFF(a.x.query('for $s in entity/address
                        return
                        <x>{concat(", ",($s/zip_code/text())[1]," "
                                       ,($s/town/text())[1]," "
                                       ,($s/street/text())[1]," "
                                       ,($s/house_number/text())[1],"/"
                                       ,($s/flat_number/text())[1]
                                   )}</x>')
               .query('for $a in distinct-values(/x/text()) return $a').value('.','varchar(max)'),1,2,''),'')  
from @xml.nodes('/root/Row') as a(x);

This is what I get:

And that's my desired outcome:

Here's what I've tried so far:

declare @v varchar(20)='house_number'

            select 
            isnull(STUFF(a.x.query('for $s in  entity/info/cleaned_name return <x>{concat(",",$s)}</x>').value('.','varchar(max)'),1,1,''),'') as 'Nazwa podmiotu' 
            ,isnull(STUFF(a.x.query('for $s in proceeding/signatures return <x>{concat(",",$s)}</x>').value('.','varchar(max)'),1,1,''),'') as 'Sygnatura'
            ,isnull(STUFF(a.x.query('if (sql:variable("@v")="") then for $s in entity/address
            return
            <x>{concat(", ",($s/zip_code/text())[1]," "
            ,($s/town/text())[1]," "
            ,($s/street/text())[1]," "
            ,($s/house_number/text())[1]
            )}</x>
            else                     
            <x>{concat(", ",($s/zip_code/text())[1]," "
            ,($s/town/text())[1]," "
            ,($s/street/text())[1]," "
            ,($s/house_number/text())[1],"/"
            ,($s/flat_number/text())[1]
            )}</x>          
            ')                                              
            .query('for $a in distinct-values(/x/text()) return $a').value('.','varchar(max)'),1,2,''),'') 
            from @xml.nodes('/root/Row') as a(x);

I receive the following error: XQuery [query()]: The variable '$s' was not found in the scope in which it was referenced. I'm wondering if I can achieve my goal in that way.

Michael Kay · Accepted Answer · 2019-11-26 13:45:54Z

1

I'm not quite sure what problem the other answers are solving, but for the question as described in the title, you can replace

($s/house_number/text())[1],"/",($s/flat_number/text())[1]

in the concat params with

string-join((($s/house_number/text())[1], ($s/flat_number/text())[1]), '/')

which will insert the "/" only if both house-number and flat-number exist.

I can't see why the [1] subscripts are needed (perhaps to defeat static type-checking?) but I've left them in.

answered Nov 26, 2019 at 13:45

Michael Kay

165k11 gold badges97 silver badges173 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gottfried Lesigang Over a year ago

string-join() is not supported in SQL Server's very reduced XQuery engine. But it's a clever idea :-) And about the [1]: SQL Server demands for a singleton in concat()...

Yitzhak Khabinsky Over a year ago

@Arkadiusz, please go ahead and up-vote elevation of SQL Server XML functionality to match latest w3c standards: feedback.azure.com/forums/908035-sql-server/suggestions/…

Arkadiusz · Accepted Answer · 2019-11-26 11:17:25Z

I've done it other way and it works.

            select 
             isnull(STUFF(a.x.query('for $s in  entity/info/cleaned_name return <x>{concat(",",$s)}</x>').value('.','varchar(max)'),1,1,''),'') as 'Nazwa podmiotu' 
            ,isnull(STUFF(a.x.query('for $s in proceeding/signatures return <x>{concat(",",$s)}</x>').value('.','varchar(max)'),1,1,''),'') as 'Sygnatura'
            ,isnull(STUFF(a.x.query('for $s in entity/address
                        return

                                                    if ($s/flat_number="")
                                                    then
                        <x>{concat(", ",($s/zip_code/text())[1]," "
                                       ,($s/town/text())[1]," "
                                       ,($s/street/text())[1]," "
                                       ,($s/house_number/text())[1]
                                   )}</x>

                                                    else
                                                    <x>{concat(", ",($s/zip_code/text())[1]," "
                                                                            ,($s/town/text())[1]," "
                                                                            ,($s/street/text())[1]," "
                                                                            ,($s/house_number/text())[1],"/"
                                                                            ,($s/flat_number/text())[1]
                                                                            )}</x>       
                                                    ')
               .query('for $a in distinct-values(/x/text()) return $a').value('.','varchar(max)'),1,2,''),'')  
from @xml.nodes('/root/Row') as a(x);

Gottfried Lesigang · Accepted Answer · 2019-11-26 12:16:01Z

Well, in this case your last question's title (which I've changed in the meanwhile) "How to use LET" might have a revival :-)

Try this:

select 
    isnull(STUFF(a.x.query('for $s in  entity/info/cleaned_name return <x>{concat(",",$s)}</x>').value('.','varchar(max)'),1,1,''),'') as 'Nazwa podmiotu' 
,isnull(STUFF(a.x.query('for $s in proceeding/signatures return <x>{concat(",",$s)}</x>').value('.','varchar(max)'),1,1,''),'') as 'Sygnatura'
,isnull(STUFF(a.x.query('for $s in entity/address
            let $houseFlat:=if($s/flat_number/text()) 
                            then concat(($s/house_number/text())[1],"/",($s/flat_number/text())[1])
                            else string(($s/house_number/text())[1])
            return
            <x>{concat(", ",($s/zip_code/text())[1]," "
                            ,($s/town/text())[1]," "
                            ,($s/street/text())[1]," "
                            ,$houseFlat
                        )}</x>')
    .query('for $a in distinct-values(/x/text()) return $a').value('.','varchar(max)'),1,2,''),'')  
from @xml.nodes('/root/Row') as a(x);

But - at least in this case - distinct-values() will not help. The both addresses are not identical due to 1 and 1/1 in the house and street number...

Collectives™ on Stack Overflow

How to use condition in XQuery?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related