inserting a string value ''Terminal$Attr1,Attr2,Attr3,Attr4,Attr5,Attr6,@Connector$Con1,Con2,Con3,Con4,@Wire$W1,W2,W3,W4,W5,' in a table

Question

I have a query :

create table #mytable (table_id int IDENTITY(1,1), attribute_name varchar(20)) declare @strString varchar(500) set @strString ='Terminal$Attr1,Attr2,Attr3,Attr4,Attr5,Attr6,@Connector$Con1,Con2,Con3,Con4,@Wire$W1,W2,W3,W4,W5,' Set @strString=REPLACE(REPLACE(@strString,'$',','),'@','')--Added ;WITH StrCTE(start, stop) AS ( SELECT 1, CHARINDEX(',' , @strString ) UNION ALL SELECT stop + 1, CHARINDEX(',' ,@strString , stop + 1) FROM StrCTE WHERE stop > 0 ) insert into #mytable SELECT SUBSTRING(@strString , start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END) AS stringValue FROM StrCTE where SUBSTRING(@strString , start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END)<>'' --, --case attribute_name --when 'Terminal' then attribute_name like '%Attr%', --when 'Connector' then attribute_name like '%Con%' , --when 'Wire' then attribute_name like '%W%' select * from #mytable

the output which I am getting is : enter image description here

I need the output as : enter image description here

I have created family table too.. but unable to write the case and joins to get the result.

The query for family table is :

create table Family (family_id int identity,family_name varchar(300)) insert into Family values ('Terminal') insert into Family values ('Connector') insert into Family values ('Wire') select * from Family

Gottfried Lesigang · Accepted Answer · 2016-03-15 09:50:49Z

EDIT According to you comments:

DECLARE @strString VARCHAR(MAX) ='Terminal$Attr1,Attr2,Attr3,Attr4,Attr5,Attr6,@Connector$Con1,Con2,Con3,Con4,@Wire$W1,W2,W3,W4,W5,' ; SET @strString='<family name="' + REPLACE(REPLACE(REPLACE(@strString,',','</x><x>'),'$','"><x>'),'@','</x></family><family name="') + '</x></family>'; DECLARE @xml XML= CAST(@strString AS XML); WITH Families AS ( SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS FamilyID ,family.value('@name','varchar(max)') AS Family ,family.query('x') AS Children FROM @xml.nodes('family') AS The(family) ) SELECT Families.FamilyID ,Families.Family ,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS ChildID ,Child.value('.','varchar(max)') AS ChildContent INTO #tmpDataFamilies FROM Families CROSS APPLY Children.nodes('/x[text()!=""]') AS One(Child); INSERT INTO tblFamily(ID,FamilyName) --set your actual Family-Table with its actual column names here! SELECT DISTINCT FamilyID,Family FROM #tmpDataFamilies; INSERT INTO tblChildren(ID,FamilyID,ChildName)--set your actual Family-Table with its actual column names here! SELECT ChildID,FamilyID,ChildContent FROM #tmpDataFamilies; DROP TABLE #tmpDataFamilies;

DECLARE @strString VARCHAR(MAX) ='Terminal$Attr1,Attr2,Attr3,Attr4,Attr5,Attr6,@Connector$Con1,Con2,Con3,Con4,@Wire$W1,W2,W3,W4,W5,' ; SET @strString='<family name="' + REPLACE(REPLACE(REPLACE(@strString,',','</x><x>'),'$','"><x>'),'@','</x></family><family name="') + '</x></family>'; SELECT @strString SELECT CAST(@strString AS XML);

The result is an XML reflecting your families:

<family name="Terminal"> <x>Attr1</x> <x>Attr2</x> <x>Attr3</x> <x>Attr4</x> <x>Attr5</x> <x>Attr6</x> <x /> </family> <family name="Connector"> <x>Con1</x> <x>Con2</x> <x>Con3</x> <x>Con4</x> <x /> </family> <family name="Wire"> <x>W1</x> <x>W2</x> <x>W3</x> <x>W4</x> <x>W5</x> <x /> </family>

With a query like this you'd get out the values:

DECLARE @xml XML= CAST(@strString AS XML); SELECT family.value('@name','varchar(max)') ,children.value('.','varchar(max)') FROM @xml.nodes('/family') AS The(family) CROSS APPLY family.nodes('*') AS Many(children)

The result:

Terminal Attr1 Terminal Attr2 Terminal Attr3 Terminal Attr4 Terminal Attr5 Terminal Attr6 Terminal Connector Con1 Connector Con2 Connector Con3 Connector Con4 Connector Wire W1 Wire W2 Wire W3 Wire W4 Wire W5 Wire

Thank you for the answer but this is not what extacly I am looking into.
please look into the 2nd image in the questions asked.. Here I am unable to add the image.
@Rains, so you want the rows with generated IDs in two relational tables?
@Shungo : Yes. I have created the family table. Family_Id will get referred to #mytable and will be providing me the output as desired.(like image 2).

Collectives™ on Stack Overflow

inserting a string value ''Terminal$Attr1,Attr2,Attr3,Attr4,Attr5,Attr6,@Connector$Con1,Con2,Con3,Con4,@Wire$W1,W2,W3,W4,W5,' in a table

1 Answer 1

EDIT According to you comments:

previous

7 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

EDIT According to you comments:

previous

7 Comments

Linked

Related