how to build xml column and add index on xml column

Question

User-583959464 posted

I am using sql server 2017. how can I build a index on [aliases] column with datatype  nvarchar(max) in the following table.

for aliases column, the possible max length is 17200. the performance is poor when I search for that column, how to improve?

CREATE TABLE [dbo].[tbl_testing](
  [id] [bigint] NULL,
  [aliases] [nvarchar](max) NULL,

[xml_aliases] xml null              ----add one more xml_aliasess extracted from aliases column
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 GO

the following is aliases column raw data
ID aliases
1 aaaaaaaaaa;bbbbbbbbbb;cccccccccccccccccccccccccc
2 ddddddddddd;iiiiiii;i
3 nnnnnnnnnnnnnnnnnnnnn

the aliases is separated by semi-colon ';'
how can i build xml schema for [aliases] column and add a xml column to table?

for aliases column which contain multiple record per id and sepearate by ';'  

finally, how to add index on xml column to improve the performance by searching xml_aliases column?
for above example

id  xml_aliases
--  --------------------------
1   aaaaaaaaaa
1   bbbbbbbbbb
1   cccccccccccccccccccccccccc
2   ddddddddddd
2   iiiiiii
2   i
3   nnnnnnnnnnnnnnnnnnnnn

Answer 1

User-583959464 posted

anyone help

Answer 2

User1535942433 posted

Hi 20141113,

According to your description and the result you need,I think there are two steps:

1.use STRING_SPLIT split ";". So it will make one cell to multiple rows.

Just like this:

create table sp(
ProductId INT NOT NULL,
Name Varchar(50),
Tags Varchar(100)
)

Insert into sp(ProductId,Name,Tags)values(4,'FE','aaa;bbb;ccc');
Insert into sp(ProductId,Name,Tags)values(5,'FR','ddd;ff');
Insert into sp(ProductId,Name,Tags)values(6,'FT','gg;bb');



SELECT ProductId, Name, value   
FROM sp  
    CROSS APPLY STRING_SPLIT(Tags, ';');  

More details,you could refer to below article:

https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017

2.retrieve formal results of a SQL query as XML.

https://docs.microsoft.com/en-us/sql/relational-databases/xml/for-xml-sql-server?view=sql-server-2017

Best regards,

Yijing Sun