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, ';');
ASP.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today. Learn more >
Member
1 Points
102 Posts
how to build xml column and add index on xml column
Mar 09, 2021 03:22 AM|20141113|LINK
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
Member
1 Points
102 Posts
Re: how to build xml column and add index on xml column
Mar 09, 2021 03:24 PM|20141113|LINK
anyone help
Contributor
4040 Points
1568 Posts
Re: how to build xml column and add index on xml column
Mar 10, 2021 06:45 AM|yij sun|LINK
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