CODEWITHSUNDEEP
sqlxml
i cant code
i cant code

Reputation: 343

SQL Extract Value from XML

I have the following XML and I want to extract the FieldValue using SQL. How can I do this?

  <Field Group="Annuitant">
    <FieldName>Contract Number</FieldName>
    <FieldValue>TR13116544</FieldValue>
  </Field>

Thanks

Upvotes: 2

Views: 73

Answers (3)

W_O_L_F
W_O_L_F

Reputation: 1486

In oracle you can do the following:

WITH TABL (FIELD) AS (
select xmltype('<Field Group="Annuitant">
    <FieldName>Contract Number</FieldName>
    <FieldValue>TR13116544</FieldValue>
  </Field>') from dual
) 

SELECT 
   EXTRACTVALUE(FIELD,'/Field/FieldName') AS FIELDNAME,
   EXTRACTVALUE(field,'/Field/FieldValue') AS FieldValue

FROM TABL  ;

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

DECLARE @xml XML=
'<Field Group="Annuitant">
    <FieldName>Contract Number</FieldName>
    <FieldValue>TR13116544</FieldValue>
 </Field>';

--the lazy approach

SELECT @xml.value('(//FieldValue)[1]','varchar(50)')

--Better

SELECT @xml.value('(/Field/FieldValue)[1]','varchar(50)')

--This is, what you should do: Be as specific as possible...

SELECT @xml.value('(/Field/FieldValue/text())[1]','varchar(50)')

If there are - what I assume - are several <Field> elements and you need to pick the rigth one, you can do something like this:

DECLARE @name VARCHAR(100)='Contract Number';
SELECT @xml.value('(/Field[(FieldName/text())[1]=sql:variable("@name")]/FieldValue/text())[1]','varchar(50)')

Hint: Your question is not very clear, that needs a lot of guessing on my side. For your next question I ask you to be more specific.

Upvotes: 2

johnsi george
johnsi george

Reputation: 218

Sample code:

DECLARE @MyXML XML
SET @MyXML ='<SampleXML>
<Fruits>
<Fruits1>Apple</Fruits1>
<Fruits2>Pineapple</Fruits2>
</Fruits>
</SampleXML>'

SELECT

a.b.value('Fruits[1]/Fruits1[1]','varchar(10)') AS Fruits1,
a.b.value('Fruits[1]/Fruits2[1]','varchar(10)') AS Fruits2

FROM @MyXML.nodes('SampleXML') a(b)

Upvotes: 0

Related Questions