1

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Velugoti
  • 59
  • 1
  • 6
  • At [this link](https://stackoverflow.com/a/43242238/5089204) you find some examples about `text()` nodes and how the might *float* within their element. – Shnugo Aug 15 '18 at 06:36

1 Answers1

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)
Rubens Farias
  • 57,174
  • 8
  • 131
  • 162
  • 1
    Good answer, you might read [this link](https://stackoverflow.com/a/43242238/5089204) about some details on `text()` – Shnugo Aug 15 '18 at 06:36