Stephen Knott Stephen Knott - 1 year ago 69
SQL Question

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...

SET @XML = '<BureauEnquiry13 xmlns:xsi="" xmlns:xsd="">

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)

Answer Source

If I understand your question

Declare @YourTable table (ID int,XMLData xml)
Insert Into @YourTable values
(1,'<BureauEnquiry13 xmlns:xsi="" xmlns:xsd=""><Surname>BLAH</Surname><Forename1>BLAHSRUNAMCE</Forename1><IdentityNo1>645654645654</IdentityNo1></BureauEnquiry13>'),
(2,'<BureauEnquiry13 xmlns:xsi="" xmlns:xsd=""><Surname>BLAH</Surname><Forename1>BLAHSRUNAMCE</Forename1><IdentityNo1>SomeOtherIdentity</IdentityNo1></BureauEnquiry13>')

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


ID  IdentityNo1
1   645654645654
2   SomeOtherIdentity
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download