Xquery nodes value
I need to get the value of AccountName in my table
<rows>
<Row xmlns="http://adcenter.microsoft.com/advertiser/repor开发者_StackOverflow中文版ting/v5/XMLSchema">
<AccountName value="MA_Yellowpages - AdStore" />
</Row>
</rows>
I am using below thing -
;WITH XMLNAMESPACES('http://adcenter.microsoft.com/advertiser/reporting/v5/XMLSchema' AS ns)
select
temp.query('AccountName[1]').value('@value','varchar(1000)') AS AC
from yp.dbo.Audit_ApiCallRawXml CROSS APPLY
RawXML.nodes('/rows/ns:Row') lg(temp)
You're almost there - but you need to also make sure to use the XML namespace on the AccountName
element that is inside the <Row>
node. Additionally, I would write your query like this:
;WITH XMLNAMESPACES('http://adcenter.microsoft.com/advertiser/reporting/v5/XMLSchema' AS ns)
SELECT
temp.value('(ns:AccountName/@value)[1]', 'varchar(1000)') AS AC
FROM
yp.dbo.Audit_ApiCallRawXml
CROSS APPLY
RawXML.nodes('/rows/ns:Row') lg(temp)
This should hopefully work then.
精彩评论