MS SQL How do we combine tables without conditions?

[inna]

There's xml with Id and the Client table, we need a sample of data: Id.id, Client.Name, each id has only one name. The current solution is the establishment of the 2nd time tables in which there is a field [Id_num] INT IDENTITY(1, 1), followed by integration, is there any other way to resolve the problem?

-- Предусловие 
CREATE TABLE #Client (Name nvarchar (10))
INSERT INTO #Client VALUES('Alisa')

INSERT INTO #Client VALUES('Bob')

INSERT INTO #Client VALUES('Eva')
-- Запрос

DECLARE @Ids nvarchar = N'<?xml version="1.0" encoding="utf-16"?>

<ArrayOfLong xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<long>1</long>

<long>2</long>

<long>3</long>

</ArrayOfLong>'
DECLARE @DocHandle INT

EXEC sp_xml_preparedocument @DocHandle OUTPUT, @Ids
CREATE TABLE #Ids (

Id_num INT IDENTITY(1, 1)

,Id BIGINT

)
INSERT INTO #Ids (Id)

SELECT id

FROM OPENXML(@DocHandle, '/ArrayOfLong/long', 8) WITH (id BIGINT '.')
CREATE TABLE #TempClients (

Id_num INT IDENTITY(1, 1)

,Name nvarchar (10)

)
INSERT INTO #TempClients (Name) SELECT Name FROM #Client
SELECT [Id], [Name]

FROM #Ids AS id

JOIN #TempClients AS client ON client.Id_num = id.Id_num
DROP TABLE #Client

DROP TABLE #Ids

DROP TABLE #TempClients

EXEC sp_xml_removedocument @DocHandle

[Kadir]

It's not possible to create so many time tables (as a last resort, I would have created one to read id from xml, since joining xml type is not always fast).

Our data are:

declare @client table (name nvarchar (10))
insert into @Client values
    ('Alisa'), ('Bob'), ('Eva')
declare @ids xml =

'<idlist>

<long>3</long>

<long>11</long>

<long>17</long>

</idlist>'

Request:

;with

id as (

select

rn = row_number() over (order by t.c),

id = t.c.value('text()[1]', 'int')

from @ids.nodes('/*[1]/long') t(c)

),

cl as (

select

rn = row_number() over (order by name),

name

from @client

)

select id.id, cl.name

from id

join cl on cl.rn = id.rn

I mean, we're numbering all the id, and we're numbering all the clients, and then we'll compare them to the number.

If they're going in order, the xml seems to be superfluous, because you can give out the id and make it easier:

select

id = row_number() over (order by name),

name

from @client