Query XML Column - Element value

Question

This should be a simple one.. just missing something obvious.

Have an XML column.. and want to strip an elements value out.

All I want is the value of the IdentityNo1 "field" . There will be only 1.

Tried a whole whack of options... and just seem to be missing on all of them... they all return NULL / Blank..... ( 3 samples below...)

Any quick pointers as to an easy solution ? (Sure I am missing an @ or a . etc).

Thanks in Advance...

DECLARE @XML xml
SET @XML = '<BureauEnquiry13 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Surname>BLAH</Surname>
  <Forename1>BLAHSRUNAMCE</Forename1>
  <IdentityNo1>645654645654</IdentityNo1>
</BureauEnquiry13>'

SELECT @XML.query('/BureauEnquiry13/IdentityNo1/.').value('.', 'varchar(50)') as IdentityNo1
select @XML.value('(/BureauEnquiry13/IdentityNo1/.)[1]', 'varchar(50)') as IdentityNo1
SELECT Nodes.Node.value('(IdentityNo1)[1]', 'VARCHAR(50)') 'IdentityNo1'
FROM     @XML.nodes('/BureauEnquiry13') Nodes(Node)

please, inform/tag witch rdbms are you using.

McNets
– McNets

2016-11-14 21:05:32 +00:00
Commented Nov 14, 2016 at 21:05 — McNets
– McNets, Commented Nov 14, 2016 at 21:05

John Cappelletti · Accepted Answer · 2016-11-14 20:48:28Z

1

If I understand your question

Declare @YourTable table (ID int,XMLData xml)
Insert Into @YourTable values
(1,'<BureauEnquiry13 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><Surname>BLAH</Surname><Forename1>BLAHSRUNAMCE</Forename1><IdentityNo1>645654645654</IdentityNo1></BureauEnquiry13>'),
(2,'<BureauEnquiry13 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><Surname>BLAH</Surname><Forename1>BLAHSRUNAMCE</Forename1><IdentityNo1>SomeOtherIdentity</IdentityNo1></BureauEnquiry13>')

Select ID
      ,IdentityNo1 = XMLData.query('/BureauEnquiry13/IdentityNo1/.').value('.', 'varchar(50)')
 From @YourTable

Returns

ID  IdentityNo1
1   645654645654
2   SomeOtherIdentity

answered Nov 14, 2016 at 20:48

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

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

Collectives™ on Stack Overflow

Query XML Column - Element value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related