How to retrieve data from XML column in sql server with no element name

Question

I have XML like this in a column of a SQL Server table:

<Sales>
  <customer>
    <custID>6886903</custID>
    <placeID>143144</placeID>UNKNOWN</customer>

</Sales>

How to retrieve UNKNOWN from above when it doesn't have any element name?

At this link you find some examples about text() nodes and how the might float within their element. — Gottfried Lesigang
– Gottfried Lesigang, Commented Aug 15, 2018 at 6:36

Rubens Farias · Accepted Answer · 2018-08-14 20:01:21Z

4

Actually, the UNKNOWN text belongs to the customer element.

DECLARE @data XML
SELECT  @data = '<Sales>
  <customer>
    <custID>6886903</custID>
    <placeID>143144</placeID>UNKNOWN</customer>

</Sales>'

SELECT  p.value('(./custID)[1]' , 'int') AS custID,
        p.value('(./placeID)[1]', 'int') AS placeID,
        p.value('(./text())[1]' , 'varchar(max)') AS customerName
FROM    @data.nodes('/Sales/customer') t(p)

edited Aug 14, 2018 at 20:01

answered Aug 14, 2018 at 19:39

Rubens Farias

58k8 gold badges136 silver badges165 bronze badges

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

1 Comment

Gottfried Lesigang Over a year ago

Good answer, you might read this link about some details on text()

Collectives™ on Stack Overflow

How to retrieve data from XML column in sql server with no element name

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related