CODEWITHSUNDEEP
sql-serverxpathxquery
Meta Mussel
Meta Mussel

Reputation: 590

XPath with SQL Server with no namespace

I have a text field called image that holds XML. A subset is below. There is not a defined namespace so I am using a wildcard '*'.

<?xml version="1.0"?>
<NewDataSet>
  <delivery>
    <ticket>A430901474</ticket>
    <revision>00A</revision>
  </delivery>

  <tickets>
    <ticket>A430901474</ticket>
    <revision>00A</revision>

    <state>VA</state>
    <county>PRINCE WILLIAM</county>
    <lot>1</lot>
    <st_from_address>15414</st_from_address> 
  </tickets>
</NewDataSet>

The query below seems to be working but it is also returning the tags. I only want the value of attribute, if the tag is there.

DECLARE @x xml
SET @x =  (SELECT [image]
  FROM [QM].[dbo].[ticket]
  where ticket_id = 83831107)
SELECT @x.query('(/*:NewDataSet/*:tickets/*:lot)[1]')

Right now I get back:

**LotNo**
<lot>1</lot>

Upvotes: 1

Views: 585

Answers (2)

canon
canon

Reputation: 41675

query() returns xml... so, it's ill-suited to your task. Since you're only requesting a single value, you probably want to use value() instead (fiddle):

select @xml.value('(/*:NewDataSet/*:tickets/*:lot)[1]','int')

Assuming, for the sake of argument, that you could have multiple lot nodes in there, you could use the nodes() method like so (fiddle):

select 
 t.c.value('.', 'int') as Lot
from @xml.nodes('/*:NewDataSet/*:tickets/*:lot') as t(c)

Upvotes: 3

JLRishe
JLRishe

Reputation: 101672

You should be able to use the text() node-test to get just the text values:

DECLARE @x xml
SET @x =  (SELECT [image]
  FROM [QM].[dbo].[ticket]
  where ticket_id = 83831107)
SELECT @x.query('(/*:NewDataSet/*:tickets/*:lot/text())[1]')

Upvotes: 1

Related Questions