Extract data from xml

Question

i came to know that the below sql can be used to extract data from xml

--XML DATA SAMPLE        
DECLARE @xmlvar xml      
SET @xmlvar='      
<NewDataSet>      
  <param>      
    <SearchField>JID</SearchField>      
    <FilterCondition>%</FilterCondition>      
    <ConditionData>4000</ConditionData>      
    <MatchCase>0</MatchCase>      
    <Table>MyTableName</Table>      
  </param>      
  <param>      
    <SearchField>Specialist</SearchField>      
    <FilterCondition>=</FilterCondition>      
    <ConditionData>Nigel Graham</ConditionData>      
    <MatchCase>0</MatchCase>      
    <Table>MyTableName</Table>      
  </param>      
</NewDataSet>'  

SELECT  A.B.value('(SearchField)[1]', 'VARCHAR(255)' ) SearchField,
A.B.value('(FilterCondition)[1]', 'VARCHAR(25)' ) Operator,
A.B.value('(ConditionData)[1]', 'VARCHAR(MAX)' ) ConditionData,
A.B.value('(MatchCase)[1]', 'BIT' ) MatchCase,
A.B.value('(Table)[1]', 'VARCHAR(MAX)' ) TableName
FROM    @WhereClause_XML.nodes('/NewDataSet/param') A(B)

the above xml is working but i am not familiar with the above type of sql. so please tell me what is the meaning of (FilterCondition)[1] or (ConditionData)[1] why bracket [1] why not bracket [0] or [2].

please explain me how above xml works. thanks

why not talk to the person who gave you this code and have them explain it to you? — Stealth Rabbi, Jan 04 '12 at 17:57
yes i asked but got no answer....if u know then plzz help me. — Thomas, Jan 04 '12 at 18:00

score 1 · Answer 1 · answered Jan 04 '12 at 18:05

Basically, the FROM clause here:

FROM @WhereClause_XML.nodes('/NewDataSet/param') A(B)

creates a "pseudo table" of XML fragments - it enumerates all XML nodes <NewDataSet>/<param> and creates that pseudo table (called A) with a pseudo column B of type XML.

These columns will contain the values of <param> - and the SELECT is reaching into these XML nodes

SELECT A.B.value('(SearchField)[1]', 'VARCHAR(255)' ) SearchField

here, from the <param> node, the first <SearchField> XML element value is retrieved and returned as a column called SearchField of type VARCHAR(255).

From the looks of your code, each of the <param> node probably looks something like:

<param>
    <SearchField>......</SearchField>
    <FilterCondition>....</FilterCondition>
    <ConditionData>....</ConditionData>
    <MatchCase>....</MatchCase>
    <Table>....</Table>
</param>

and all those values are extracted into columns of your SELECT statement.

Mikael Eriksson · Accepted Answer · 2012-01-04T18:55:07.113

1

The value() function returns one value from your XML so the XPath used must point to one specific node. [1] in (SearchField)[1] means that you want the value from the first occurrence of SearchField in the XML. [2] would then of course give you the second occurrence. Without [1] you are asking for all occurrences of SearchField.

Update: Sample code:

declare @x xml = 
'<root>
  <item>1</item>
  <item>2</item>
</root>'

select @x.value('(root/item)[1]', 'int') as One,
       @x.value('(root/item)[2]', 'int') as Two

Result:

One         Two
----------- -----------
1           2

edited Jan 04 '12 at 18:55

answered Jan 04 '12 at 18:14

Mikael Eriksson

136,425
22
210
281

can u please show me some more usage of similar data extraction from xml. u said -- 1 means that you want the value from the first occurrence of SearchField in the XML. [2] would then of course give you the second occurrence. Without [1] you are asking for all occurrences of SearchField. come with some sample code where u use [1] and [2] and without [1]. so i can issue ur code in sql server and then try to understand how things work. thanks – Thomas Jan 04 '12 at 18:28
@Thomas You can have a look at the questions and answers tagged with xquery-sql. http://stackoverflow.com/questions/tagged/xquery-sql – Mikael Eriksson Jan 04 '12 at 18:36
thanks sure i will visit link but it would be better if provide 2 or 3 small sample for [1] or [2]. if possible please give me sample query – Thomas Jan 04 '12 at 18:50
@Thomas added a sample to the answer. – Mikael Eriksson Jan 04 '12 at 18:55

Extract data from xml

2 Answers2