Query XML with a Unique tag situation

Question

Thanks in advance for your help! I am trying to write a query on an XML column that is set up as follows:

<DictionaryOfStringObject>
 <Item>
  <Key>
  <string>Info</string>
  </Key>
  <Value>
   <SerializableDictionaryOfStringObject>    
     <Item>
      <Key>
        <string>FirstName</string>
      </Key>
      <Value>
        <anyType xmlns:q1="http://www.w3.org/2001/XMLSchema" xmlns:d7p1="http://www.w3.org/2001/XMLSchema-instance" d7p1:type="q1:string">JON</anyType>
      </Value>
     </Item>
     <Item>
      <Key>
        <string>MiddleName</string>
      </Key>
      <Value>
        <anyType xmlns:q1="http://www.w3.org/2001/XMLSchema" xmlns:d7p1="http://www.w3.org/2001/XMLSchema-instance" d7p1:type="q1:string" />
      </Value>
    </Item>
    <Item>
      <Key>
        <string>LastName</string>
      </Key>
      <Value>
        <anyType xmlns:q1="http://www.w3.org/2001/XMLSchema" xmlns:d7p1="http://www.w3.org/2001/XMLSchema-instance" d7p1:type="q1:string">SNOW</anyType>
      </Value>
    </Item>
    <Item>
      <Key>
        <string>Gender</string>
      </Key>
      <Value>
        <anyType xmlns:q1="http://www.w3.org/2001/XMLSchema" xmlns:d7p1="http://www.w3.org/2001/XMLSchema-instance" d7p1:type="q1:string">Male</anyType>
      </Value>
    </Item>

I need to pull out the information in this column FOR EACH ROW IN THE TABLE. I am not looking for a specific person. I need to extract this information from the column for each row in the table.

I understand querying a single row for a specific person but how can I develop a SELECT statement that will extract this information across the entire table for each row?

Each person is given a row in the Table and an XML column I need to develop a query to run against the table that will extract the information for each person.

Row 1 =  FirstName1, LastName1, Gender1   
Row 2 =  FirstName2, LastName2, Gender2

I have never written a query for tags like this, can someone please assist me in developing a Select statement(s) for SQL Server 2008 and SQL Server 2010.

I have some XML experience but could use an example and and explanation of how this is achieved.

See answer here: http://stackoverflow.com/questions/10344553/how-can-i-query-a-value-in-sql-server-xml-column. Another good source of how to XML in SQL Server, http://www.brokenwire.net/bw/Programming/125/querying-xml-fields-using-t-sql — TTeeple, Jun 03 '15 at 16:51
Thanks for the reply I realized I need to Edit my question now. Those do not reply to my question directly. I am not searching for a specific first name combination I want to select this information for each row of the table. — Dylan Haines, Jun 03 '15 at 18:26
Your XML appears to be cut off. Can you post the whole thing? — Code Different, Jun 03 '15 at 19:55

score 0 · Answer 1 · answered Jun 03 '15 at 20:02

0

You can use XPATH to achive this like this

select btrim(xpath('/DictionaryOfStringObject/Item/Value/SerializableDictionaryOfStringObject/item/key/string/text()', column_name)::text,'{"}') from table

try it should work

answered Jun 03 '15 at 20:02

Tushar Purohit

42
6

score 0 · Answer 2 · edited Mar 17 '17 at 10:37

0

possible query :

SELECT 
  x.value('(Item[Key/string="FirstName"]/Value/anyType)[1]', 'varchar(500)') 
      as 'FirstName',
  x.value('(Item[Key/string="LastName"]/Value/anyType)[1]', 'varchar(500)') 
      as 'LastName',
  x.value('(Item[Key/string="Gender"]/Value/anyType)[1]', 'varchar(500)') 
      as 'Gender'
FROM MyTable t
  OUTER APPLY t.xml.nodes('/*/Item/Value/*') v(x)

SQL Fiddle Demo

brief explanation :

Basically, the query do OUTER APPLY on a parent tag that carries all information to be outputted as columns, the parent tag is <SerializableDictionaryOfStringObject> tag in this case. Notice that I use * just to avoid lengthy tag names in the xpath.

Then in the SELECT clause, the query simply filter <Item> tag by it's Key/string child value to determine which <Item> corresponds to which column. And then from each of matching <Item> it returns corresponding Value/anyType child.

example output :

enter image description here

_{side note : for future questions, please provide, at least, a well-formed XML sample and exact expected query output given that sample XML as input. SQL fiddle would be a perfect addition for SQL question like this, see : Tips for asking a good Structured Query Language (SQL) question}

edited Mar 17 '17 at 10:37

Community

1
1

answered Jun 04 '15 at 01:46

har07

88,338
12
84
137

I really appreciate your answer. I am attempting the query but I am getting NULL returns. I know it is my own fault because of my poorly constructed question. – Dylan Haines Jun 04 '15 at 22:31
Care to improve your question? Create sql fiddle containing sample data and the query I suggested, showing that the result is `NULL`. – har07 Jun 05 '15 at 00:53
Sorry for the delayed response @har07. Your query and explanation have been really helpful. I have gotten the query to work... in a sense, I structured my query almost identically to yours but I am receiving some extra NULL values. ____________________ |CandidateID| FirstName| – Dylan Haines Jun 08 '15 at 15:16
Firstname = Jon, followed by THREE NULLs for the same candidate – Dylan Haines Jun 08 '15 at 15:37
It's hard to help further without being able to reproduce your problem (as seen in the sql fiddle link, I didn't get any NULL) – har07 Jun 08 '15 at 22:03

Query XML with a Unique tag situation

2 Answers2