CODEWITHSUNDEEP
sqlsql-serverxmlt-sqlxml-parsing
JK76
JK76

Reputation: 11

How to get tag attribute value from xml

I have a xml where the values are given:

<User version="5.1.0.1" ... (omitted for brevity) >
  <Login usewinLogin="true" passwordNeverExpires="false" />
  <Misc />
  <AdditionalInfo />
  <OutOfOfficeSettings isOutOfOffice="false" startDateTime="0001-01-01T00:00:00.0000000Z" />
  <RegionalSettings language="de" culture="de" />
</User>`

I would need the value from OutofOfficeSettings isOutofOffice= (Options are false or true).

I never did this before, is someone here to help me?

Upvotes: 1

Views: 36

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81930

One option 

Declare @DWUser  table (Active int, settings xml )
Insert Into @DWUser  values 
 (1,'<User version="5.1.0.1" uid="1" oid="2" w3uid="ADMIN" guid="05b6f2a2-cdf8-4b73-8d1b-86eab657654" oguid="43a0d394-e515-45ea-aa25-7822673c52a3" name="admin" active="true" eMail="[email protected]" distribution="NetworkStartup" securityLevel="Normal" defaultWebBasket="cc1c589a-549a-4957-959b-3b5acc4decc9"><Login usewinLogin="true" passwordNeverExpires="false" /><Misc /><AdditionalInfo /><OutOfOfficeSettings isOutOfOffice="false" startDateTime="0001-01-01T00:00:00.0000000Z" /><RegionalSettings language="de" culture="de" /></User>')
,(1,'<root><OtherXML>Hi</OtherXML></root>')


Select A.active
      ,Type = X.attr.value('@isOutOfOffice','varchar(100)')
 From @DWUser A
 Outer Apply A.settings.nodes('User/OutOfOfficeSettings') as X(attr)
 Where A.Active=1

Returns

active  Type
1       false
1       NULL

Upvotes: 2

Related Questions