Sql: How to Get the Value of an Attribute in Xml Datatype

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

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 retrieve value of attribute in xml datatype based on parameter name in SQL Server 2008 R2?

You can use /diagnostics/@* to get a list of attributes and the compare with local-name() to get the one you want.

declare @x xml 
set @x = '<diagnostics CurrentIterationNumber="79" TotalItemsProcessed="37847"
ItemsProcessedLastIteration="75" ItemsProcessedPerIteration="479" />'

declare @property as varchar(50);
set @property = 'CurrentIterationNumber';

select @x.value('(/diagnostics/@*[local-name() = sql:variable("@property")])[1]', 'varchar(50)')

Or like this if you expect a list of values instead of only one as in your sample XML.

select t.c.value('.', 'varchar(50)')
from @x.nodes('/diagnostics/@*[local-name() = sql:variable("@property")]') as t(c)

Note: There should not be a @ in the search string. set @property = 'CurrentIterationNumber';

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

Extract all attribute values from XML column in SQL Server

I had to repair your XML, as the provided sample is not well-formed. So you might need to adapt this.

DECLARE @xml XML=
N'<ns2:pay xmlns:ns2="http://someurl.com/">
<ns2:e k="BID">
<ns2:l v="2"/>
</ns2:e>
<ns2:e k="PMD">
<ns2:l v="1"/>
</ns2:e>
<ns2:e k="GPTA5">
<ns2:s v=""/>
</ns2:e>
<ns2:e k="GPTA4">
<ns2:s v=""/>
</ns2:e>
<ns2:e k="GPTA3">
<ns2:s v="572"/>
</ns2:e>
<ns2:e k="GPTA1">
<ns2:s v="Sweet & Sour Sauce"/>
</ns2:e>
<ns2:e k="PFID">
<ns2:l v="1"/>
</ns2:e>
<ns2:e k="EAN">
<ns2:s v="010000"/>
</ns2:e>
<ns2:e k="PT">
<ns2:s v="1"/>
</ns2:e>
<ns2:e k="TXID1">
<ns2:l v="0"/>
</ns2:e>
<ns2:e k="PMN">
<ns2:l v="1"/>
</ns2:e>
<ns2:e k="DID">
<ns2:l v="1"/>
</ns2:e>
<ns2:e k="GPTA6">
<ns2:s v=""/>
</ns2:e>
<ns2:e k="GPTA7">
<ns2:s v=""/>
</ns2:e>
<ns2:e k="PLU">
<ns2:l v="10000"/>
</ns2:e>
<ns2:e k="GPTA8">
<ns2:s v=""/>
</ns2:e>
<ns2:e k="DYT">
<ns2:s v="SWEET & SOUR SAUCE"/>
</ns2:e>
</ns2:pay>';

--This query will return all attributes with their names (a classic key-value-list)

WITH XMLNAMESPACES('http://someurl.com/' AS ns2)
SELECT e.value('@k','nvarchar(max)') AS AttributeName
,e.value('(ns2:l/@v)[1]','nvarchar(max)') AS AttributeValue
FROM @xml.nodes(N'/ns2:pay/ns2:e') A(e);

--This query allows you to pick the value of one given key

DECLARE @FindThis NVARCHAR(100)='BID';
WITH XMLNAMESPACES('http://someurl.com/' AS ns2)
SELECT @xml.value(N'(/ns2:pay/ns2:e[@k=sql:variable("@FindThis")]/ns2:l/@v)[1]','int'); --use the proper type, if all values will be fine with this

--This query will return a table of your values (as long as you knwo all keys in advance)

WITH XMLNAMESPACES('http://someurl.com/' AS ns2)
SELECT @xml.value(N'(/ns2:pay/ns2:e[@k="BID"]/ns2:l/@v)[1]','int') AS BID
,@xml.value(N'(/ns2:pay/ns2:e[@k="PMD"]/ns2:l/@v)[1]','nvarchar(max)') AS PMD
,@xml.value(N'(/ns2:pay/ns2:e[@k="GPTA4"]/ns2:l/@v)[1]','nvarchar(max)') AS GPTA4
--add all keys in the same way...

UPDATE

Here's an example to read this from a table using PIVOT to get it in tabular form:

Hint: I use NVARCHAR(1000) to simulate your need for a cast on-the-fly:

DECLARE @mockupTable TABLE(ID INT,YourData NVARCHAR(1000));
INSERT INTO @mockupTable VALUES
(1
,N'<ns2:pay xmlns:ns2="http://someurl.com/">
<ns2:e k="BID">
<ns2:l v="2"/>
</ns2:e>
<ns2:e k="PMD">
<ns2:l v="1"/>
</ns2:e>
<ns2:e k="GPTA5">
<ns2:s v=""/>
</ns2:e>
<ns2:e k="GPTA4">
<ns2:s v=""/>
</ns2:e>
<ns2:e k="GPTA3">
<ns2:s v="572"/>
</ns2:e>
<!--shortened for brevity-->
</ns2:pay>')
,(2
,N'<ns2:pay xmlns:ns2="http://someurl.com/">
<ns2:e k="BID">
<ns2:l v="20"/>
</ns2:e>
<ns2:e k="PMD">
<ns2:l v="10"/>
</ns2:e>
<ns2:e k="GPTA5">
<ns2:s v="bla"/>
</ns2:e>
<ns2:e k="GPTA4">
<ns2:s v=""/>
</ns2:e>
<ns2:e k="GPTA3">
<ns2:s v="572"/>
</ns2:e>
<!--shortened for brevity-->
</ns2:pay>');

--The query will create a key-value list with the row's ID as grouping factor

WITH XMLNAMESPACES('http://someurl.com/' AS ns2)
SELECT p.*
FROM
(
SELECT ID
,e.value('@k','nvarchar(max)') AS AttributeName
,e.value('(ns2:l/@v)[1]','nvarchar(max)') AS AttributeValue
FROM @mockupTable t
--the cast happens here
CROSS APPLY(SELECT CAST(t.YourData AS XML)) A(TheXml)
--the call to .nodes() happens here to return a derived table
CROSS APPLY TheXml.nodes(N'/ns2:pay/ns2:e') B(e)
) tbl
PIVOT(MAX(AttributeValue)
FOR AttributeName
IN(BID,PMD,GPTA3,GPTA4,GPTA5) --add your columns here, order does not matter
) p

How to extract attribute value from XML in SQL Server 2019 (v15)?

With no expected results, perhaps this is enough to get you started.

As you define a default namespace only once you get to status, you can't use a DEFAULT namespace in XMLNAMESPACES, so I name it ns and reference that instead. This gives you the value of all the terms and their id attribute:

DECLARE @XML xml = '<Individual xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Content>
<status xmlns:d3p1="http://www.uc.se/schemas/ucOrderReply/" xmlns="http://www.uc.se/schemas/ucOrderReply/" d3p1:result="ok" />
<uc xmlns="http://www.uc.se/schemas/ucOrderReply/">
<xmlReply>
<reports xmlns:d5p1="http://www.uc.se/schemas/ucOrderReply/" d5p1:lang="eng">
<report d5p1:id="7605089247" d5p1:name="Test1 Test2" d5p1:styp="K39" d5p1:index="0">
<group d5p1:id="W080" d5p1:index="0" d5p1:key="" d5p1:name="ID particulars">
<term d5p1:id="W08001">9760508923</term>
<term d5p1:id="W08002">7605089277</term>
<term d5p1:id="W08003">Test1</term>
<term d5p1:id="W08004">Test2</term>
</group>
<group d5p1:id="W1A0" d5p1:index="0" d5p1:key="" d5p1:name="UC RPB">
<term d5p1:id="W1A003">000000000000000022</term>
<term d5p1:id="W1A081">2,2 %</term>
<term d5p1:id="W1A082">2,18839</term>
</group>
</report>
</reports>
</xmlReply>
</uc>
</Content>
</Individual>';
WITH XMLNAMESPACES('http://www.uc.se/schemas/ucOrderReply/' AS ns,'http://www.uc.se/schemas/ucOrderReply/' AS d5p1)
SELECT X.g.value('(@d5p1:id)','varchar(20)') AS id,
X.g.value('(text())[1]','varchar(20)') AS term
FROM @XML.nodes('Individual/Content/ns:uc/ns:xmlReply/ns:reports/ns:report/ns:group/ns:term') X(g);

I note that the XML has been changed since the initial version I used to write this answer. This answer has not been (read "won't be") adjusted for that.

Extracting Attributes from XML Fields in SQL Server 2008 Table

Just after I posted the question, I stumbled across this answer. Don't know why I couldn't find it in prior searches. It was the answer I was looking for. Here is the query that works:

Query

select Name
,xml_data.value('(/data/info/@x)[1]', 'int') as [Info.x]
,xml_data.value('(/data/info/@y)[1]', 'int') as [Info.y]
,xml_data.value('(/data/info/.)[1]', 'varchar(10)') as [Info]
from #temp

Result

Name     Info.x    Info.y    Info
------- -------- -------- ---------
one 42 99 Red
two 27 72 Blue
three 16 51 Green
four 12 37 Yellow

.

------ Edit [2014-01-29] ------

I found another case that is worth adding to this answer. Given multiple <info> elements within the <data> element, it is possible to return all <info> nodes by using cross apply:

create table #temp (id int, name varchar(32), xml_data xml)

insert into #temp values
(1, 'one', '<data><info x="42" y="99">Red</info><info x="43" y="100">Pink</info></data>'),
(2, 'two', '<data><info x="27" y="72">Blue</info><info x="28" y="73">Light Blue</info></data>'),
(3, 'three', '<data><info x="16" y="51">Green</info><info x="17" y="52">Orange</info></data>'),
(4, 'four', '<data><info x="12" y="37">Yellow</info><info x="13" y="38">Purple</info></data>')

select Name
,C.value('@x', 'int') as [Info.x]
,C.value('@y', 'int') as [Info.y]
,C.value('.', 'varchar(10)') as [Info]
from #temp cross apply
#temp.xml_data.nodes('data/info') as X(C)

drop table #temp

This example returns the following dataset:

Name      Info.x      Info.y      Info
--------- ----------- ----------- ----------
one 42 99 Red
one 43 100 Pink
two 27 72 Blue
two 28 73 Light Blue
three 16 51 Green
three 17 52 Orange
four 12 37 Yellow
four 13 38 Purple

Extract value and attributes from xml in sql column

This is a way to get all values out as is:

Your XML reduced:

DECLARE @xml XML=
'<settings>
<s key="isvisionimpaired" type="Bit" value="0" />
<s key="insurancerequesturl" type="String" value="" />
<s key="haswheelstudio" type="Bit" value="0" />
</settings>'

SELECT a.b.value('@key','varchar(max)') AS s_key
,a.b.value('@type','varchar(max)') AS s_type
,a.b.value('@value','varchar(max)') AS s_value
FROM @xml.nodes('settings/s') AS a(b);

Another approach might be - if you know all the possible data - to write all of them one after the other:

SELECT @xml.value('(/settings/s[@key="isvisionimpaired"]/@value)[1]','bit') AS isvisionimpaired
,@xml.value('(/settings/s[@key="insurancerequesturl"]/@value)[1]','varchar(max)') AS insurancerequesturl
,@xml.value('(/settings/s[@key="haswheelstudio"]/@value)[1]','bit') AS haswheelstudio
--all possible data here

In this case missing values will just return empty...

The advantage: You can specify the correct data type and use a "speaking" column name (in your case the key's name probably).

A third approach might be dynamic SQL where you generate the statement using all information from within your XML to generate a statement exactly the same as I did it above. Doing so you can specify column names and datatypes dynamically.

Finally you'd need an EXEC which makes it impossible to integrate this approach in ad-hoc queries such as a VIEW.

EDIT: This should be what you need:

According to your comment

SELECT *
,[site_settings].value('(/settings/s[@key="isvisionimpaired"]/@value)[1]','bit') AS isvisionimpaired
,[site_settings].value('(/settings/s[@key="insurancerequesturl"]/@value)[1]','varchar(max)') AS insurancerequesturl
,[site_settings].value('(/settings/s[@key="haswheelstudio"]/@value)[1]','bit') AS haswheelstudio
--all possible data here
FROM [sites]


Related Topics



Leave a reply



Submit