CODEWITHSUNDEEP
sqlsql-serverxmlelement
Stephen Knott
Stephen Knott

Reputation: 23

Query XML Column - Element value

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)

Upvotes: 1

Views: 51

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81930

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

Upvotes: 1

Related Questions