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 term
s 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
How to Backup a Remote SQL Server Database to a Local Drive
Selecting Top N Rows for Each Group in a Table
How to Convert Postgresql 9.4's JSONb Type to Float
How to Cast a String to Integer and Have 0 in Case of Error in the Cast with Postgresql
Is There a Difference Between Select * and Select [List Each Col]
Table or Column Name Cannot Start with Numeric
When to Use Except as Opposed to Not Exists in Transact SQL
Sql: How to Select Only Groups That Do Not Contain a Certain Value
How to Show Row Numbers in Postgresql Query
How to Get N Rows Starting from Row M from Sorted Table in T-Sql
Boolean VS Tinyint(1) for Boolean Values in MySQL
How to Concatenate Numbers and Strings to Format Numbers in T-Sql
How to Remove Time from Datetime
Pivot Query on Distinct Records