sql - Select multiple substrings from same column

Question

I have column(name-xml) value from message table as below (in XML format)

<Line>
    <ID>12345</ID>
    <ErrorText>sky is red</ErrorText>
</Line>
<Line>
    <ID>54321</ID>
    <ErrorText>roses are white</ErrorText>
</Line>
<Line>
    <ID>98765</ID>
    <ErrorText>Sun rises in the east</ErrorText>
</Line>

From here I need data as below

ID      ErrorText
12345   sky is red
54321   roses are white
98765   Sun rises in the east

I need 2 separate columns from the same DB table column. How

Which database are you using and what version? Answers will be product dependent. — Kamil Gosciminski
– Kamil Gosciminski, Commented Feb 22, 2019 at 8:27
Why on earth are you storing data like this? Wouldn't it be better to instert in into a table with a couple of columns? Eg. ErrorText, LineId, etc. I can provide you with an MSSQL script for this but it will be slow for large sets of data — Kristóf Tóth
– Kristóf Tóth, Commented Feb 22, 2019 at 8:30

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

Try it like this

DECLARE @mockup TABLE(ID INT IDENTITY, YourXmlColumn XML);
INSERT INTO @mockup VALUES
(
N'<Line><ID>12345</ID><ErrorText>sky is red</ErrorText></Line><Line><ID>54321</ID><ErrorText>roses are white</ErrorText></Line><Line><ID>98765</ID><ErrorText>Sun rises in the east</ErrorText></Line>'
);

SELECT ln.value('(ID/text())[1]','int') AS ID
      ,ln.value('(ErrorText/text())[1]','nvarchar(max)') AS ID
FROM @mockup m
CROSS APPLY m.YourXmlColumn.nodes('/Line') A(ln);

CROSS APPLY allows you to call the native XML.method .nodes(). The XPath "/Line" will return each repeating <Line>-element as a separate row. The method .value() allows you to pick the element's content.

UPDATE

An example how to cast the deprecated TEXT type. I use a CTE to do the cast.

WITH casted AS
(
    SELECT t.*
          ,CAST(CAST(t.YourXmlColumn AS VARCHAR(MAX)) AS XML) AS ToXml
    FROM YourTable t
    --WHERE...  (filters go here)
)
SELECT ln.value('(ID/text())[1]','int') AS ID
      ,ln.value('(ErrorText/text())[1]','nvarchar(max)') AS ID
FROM casted
CROSS APPLY casted.ToXml.nodes('/Line') A(ln);

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Feb 22, 2019 at 8:56

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

2 Comments

Shyama Over a year ago

This is working if I execute it as it is.. But i need to use insert into @mockup select XML from message and then execute the last query. When i am doing this, I am getting empty rows. For what it's worth, the XML column in my table is of the data type Text but not XML

Gottfried Lesigang Over a year ago

@Shyama I don't know if I get this correctly... You do not have to use my mockup-table at all. Just use your original table and column name in the same place. And about TEXT: This type is deprecated since v2005!. In computer thinking this means centuries. You will need a CAST to VARCHAR(MAX) and from there you can further CAST to XML. You really should try to use the native XML type if this is under your control. This will be faster and more secure.

Milad · Accepted Answer · 2019-02-22 11:54:56Z

0

try this :

DECLARE @XML XML = '
    <Line>
        <ID>12345</ID>
        <ErrorText>sky is red</ErrorText>
    </Line>
    <Line>
        <ID>54321</ID>
        <ErrorText>roses are white</ErrorText>
    </Line>
    <Line>
        <ID>98765</ID>
        <ErrorText>Sun rises in the east</ErrorText>
    </Line>'

SELECT 
    Tbl.Col.value('ID[1]', 'INT') ID
    ,Tbl.Col.value('ErrorText[1]', 'VARCHAR(100)') ErrorText
FROM 
    @XML.nodes('//Line') Tbl(Col)

answered Feb 22, 2019 at 11:54

Milad

1171 silver badge8 bronze badges

3 Comments

Shyama Over a year ago

I am using somthing like this, but I am getting Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used. Please help. DECLARE @XML XML = (select XML from message where XML like '%<Status>1</Status>%' and xml like '%ErrorText%' and TYPE='Response') SELECT Tbl.Col.value('ID[1]', 'INT') ID ,Tbl.Col.value('ErrorText[1]', 'VARCHAR(100)') ErrorText FROM @XML.nodes('//Line') Tbl(Col)

Milad Over a year ago

This query maybe return more than one row and It,s depend on your data.

Milad Over a year ago

You must limit your output by where clause or use Top 1 in select statement.

Collectives™ on Stack Overflow

sql - Select multiple substrings from same column

2 Answers 2

UPDATE

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

UPDATE

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related