CODEWITHSUNDEEP
sqlsql-serverxmlt-sql
Shyama
Shyama

Reputation: 47

sql - Select multiple substrings from same column

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

Upvotes: 1

Views: 631

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

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);

Upvotes: 1

Milad
Milad

Reputation: 117

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)

Upvotes: 0

Related Questions