SQL Server Query Xml Attribute for an Element Value

How to query for Xml values and attributes from table in SQL Server?

Actually you're close to your goal, you just need to use nodes() method to split your rows and then get values:

select
    s.SqmId,
    m.c.value('@id', 'varchar(max)') as id,
    m.c.value('@type', 'varchar(max)') as type,
    m.c.value('@unit', 'varchar(max)') as unit,
    m.c.value('@sum', 'varchar(max)') as [sum],
    m.c.value('@count', 'varchar(max)') as [count],
    m.c.value('@minValue', 'varchar(max)') as minValue,
    m.c.value('@maxValue', 'varchar(max)') as maxValue,
    m.c.value('.', 'nvarchar(max)') as Value,
    m.c.value('(text())[1]', 'nvarchar(max)') as Value2
from sqm as s
    outer apply s.data.nodes('Sqm/Metrics/Metric') as m(c)

sql fiddle demo

How to get a particular attribute from XML element in SQL Server

Try using the .value function instead of .query:

SELECT 
  xmlCol.value('(/container/param[@name="paramB"]/@value)[1]', 'varchar(50)') 
FROM  
  LogTable

The XPath expression could potentially return a list of nodes, therefore you need to add a [1] to that potential list to tell SQL Server to use the first of those entries (and yes - that list is 1-based - not 0-based). As second parameter, you need to specify what type the value should be converted to - just guessing here.

Marc

SQL Server query xml attribute for an element value

;WITH XMLNAMESPACES(DEFAULT N'http://www.kaseya.com/vsa/2007/12/ServiceDeskDefinition.xsd')
select
    T.C.value('data(.)', 'nvarchar(128)')
from [YOUR_TABLE] as Y
    outer apply Y.[YOUR_XML_COLUMN].nodes('/CustomFields/Field[@fieldName="CwTicketId"]') as T(C)

SQL Server: How to get the value of a XML element specifying an attribute?

Sure there is...

You also can shorten the declaration:

declare @X xml=
'<translations>
  <value lang="en-US">example</value>
  <value lang="de-DE">Beispiel</value>
</translations>';

select @X.value('(/translations/value[@lang="en-US"])[1]','varchar(max)');

The point is, that you need a singleton result when you are using .value(). You achieve this by putting the XPath in paranthesis and force the first element to be taken (in this case it's the only element).

Btw: If you need this (and sooner or later you will need this...), you might put the "en-US" as parameter into your query like this:

declare @prm VARCHAR(10)='en_US';
select @X.value('(/translations/value[@lang=sql:variable("@prm")])[1]','varchar(max)');

You can reach the similar, but with a value of the actual query by using sql:column().

SQL XML Attribute value

Something like this does the trick:

declare @t table (
    Id int identity(1,1) primary key,
    XMLData xml not null
);

insert into @t (XMLData)
values (N'<obj1 id="1" name="sally" />
<obj2 id="15" date="yesterday" />'),
(N'<objM />');

select t.Id, x.c.value('./@id', 'varchar(max)')
from @t t
    cross apply t.XMLData.nodes('//*[@id]') x(c);

Or, you can save a little bit if you only need value from first / single node:

select t.Id, t.XMLData.value('/*[@id][1]/@id', 'varchar(max)')
from @t t;

SQL Server: Getting value from an XML attribute with namespace different to element's

You need to be really careful with your XML namespaces.....

The <dataroot> elements define a d XML namespace which applies to this top-level element, but the <Books> underneath it defines a new default XML namespace http://tempuri.org/Books.xsd that applies to this node and its children.

Therefore, you need to use this query to get your data:

;WITH XMLNAMESPACES ('urn:schemas-microsoft-com:xml-d' AS d, 
                     'http://tempuri.org/Books.xsd' AS ns)
SELECT * 
FROM #TestXml
WHERE XMLDATA.exist('/d:dataroot/ns:Books/ns:Book[@isbn="124"]') = 1

Update: and if you want to base it on the @id attribute, use this:

;WITH XMLNAMESPACES ('urn:schemas-microsoft-com:xml-d' AS d, 
                     'http://tempuri.org/Books.xsd' AS ns)
SELECT * 
FROM #TestXml
WHERE XMLDATA.exist('/d:dataroot/ns:Books/ns:Book[@d:id="1"]') = 1

If you want to base it on the @name attribute, use this:

;WITH XMLNAMESPACES ('urn:schemas-microsoft-com:xml-d' AS d, 
                     'urn:schemas-microsoft-com:xml-m' AS m,
                     'http://tempuri.org/Books.xsd' AS ns)
SELECT * 
FROM #TestXml
WHERE XMLDATA.exist('/d:dataroot/ns:Books/ns:Book[@m:name="Book1"]') = 1

SQL XML column query for both attributes and elements

Try it like this:

SELECT  
m.c.value('@key', 'varchar(max)') as xmlkey,
m.c.value('@value', 'varchar(max)') as xmlvalue,
n.s.value('text()[1]','nvarchar(max)') AS ListValue
from #TEMPAK2 as s 
cross apply s.attributes.nodes('Attributes/Map/entry') as m(c)
outer apply m.c.nodes('value/List/String') AS n(s);

I assume, that you have two types of <entry>

• name-value pairs
• such with a <value> element

It looks like a <value> element has a <List> of <String> Structur. At least in all cases you show us.

SQL: How can I get the value of an attribute in XML datatype?

Use XQuery:

declare @xml xml =
'<email>
  <account language="en" />
</email>'

select @xml.value('(/email/account/@language)[1]', 'nvarchar(max)')

---

declare @t table (m xml)

insert @t values 
    ('<email><account language="en" /></email>'), 
    ('<email><account language="fr" /></email>')

select m.value('(/email/account/@language)[1]', 'nvarchar(max)')
from @t

Output:

en
fr

Extract attribute value from XML

GROUP_ID element has no value, i.e. child text node.
It has just attributes.

Please try the following.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
(N'<data>
    <Company>
        <GROUP_ID ControlType="xxxxxxxxx" ParentName="ppppppppp" Value="100"/>
    </Company>
</data>');
-- DDL and sample data population, end

SELECT id
    , c.value('@ControlType', 'VARCHAR(30)') AS ControlType
    , c.value('@ParentName', 'VARCHAR(30)') AS ParentName
    , c.value('@Value', 'INT') AS [Value]
FROM @tbl CROSS APPLY xmldata.nodes('/data/Company/GROUP_ID') AS t(c);

Output

+----+-------------+------------+-------+
| id | ControlType | ParentName | Value |
+----+-------------+------------+-------+
|  1 | xxxxxxxxx   | ppppppppp  |   100 |
+----+-------------+------------+-------+

---

Related Topics