SQL Query XML from Field

I have xml data in my field.  I would like to see what data is in the field.  Below is an example of the XML structure (which is stored in a text field).

<subscriber>
        <groupNumber>1234</groupNumber>
        <planName>MyPlan</planName>
        <per_last>Doe</per_last>
        <per_first>John</per_first>
        <per_ssn>123456789</per_ssn>
        <per_addr1>1 Main St</per_addr1>
        <per_addr2>#8</per_addr2>
        <per_city>New York</per_city>
        <per_state>NY</per_state>
        <per_zip>10465</per_zip>
        <per_dob>19700101</per_dob>
        <per_gender>M</per_gender>
        <subscriberClaim>
          <clm_id>1234567</clm_id>
          <clm_totalCharge>1000.00</clm_totalCharge>
        </subscriberClaim>

Open in new window


I have tried the below code

select cast(xmlField as xml) xmlField into #tmp from (
	select '<subscriber><per_first>John</per_first><per_last>Doe</per_last></subscriber>' xmlField
Union
	select '<subscriber><per_first>Jane</per_first><per_last>Doe</per_last></subscriber>' 
) tb

SELECT
    xmlField.value('(subscriber/per_first)[1]', 'nvarchar(max)') as FirstName
    ,xmlField.value('(subscriber/per_last)[1]', 'nvarchar(max)') as LastName
FROM #tmp

select xmlField.value('(/subscriber/per_first)[1]', 'nvarchar(max)') as FirstName from #tmp

drop table #tmp

Open in new window


The above code is working.  However, I would like to pull firstname, lastname, and clm_id from the XML without specifying a name.  Is there a way to query the field to get a list of the three fields?
LVL 2
CipherISAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

G GodwinDatabase AdministratorCommented:
I am not sure I understand what you are trying to do exactly.  

However, I would like to pull firstname, lastname, and clm_id from the XML without specifying a name.  Is there a way to query the field to get a list of the three fields?

This will get the fields without having to specify the instance [1] ,

select cast(xmlField as xml) xmlField 
into #tmp 
from 
(
select --'<subscriber><per_first>John</per_first><per_last>Doe</per_last></subscriber>' xmlField
'<subscriber>
        <groupNumber>1234</groupNumber>
        <planName>MyPlan</planName>
        <per_last>Doe</per_last>
        <per_first>John</per_first>
        <per_ssn>123456789</per_ssn>
        <per_addr1>1 Main St</per_addr1>
        <per_addr2>#8</per_addr2>
        <per_city>New York</per_city>
        <per_state>NY</per_state>
        <per_zip>10465</per_zip>
        <per_dob>19700101</per_dob>
        <per_gender>M</per_gender>
        <subscriberClaim>
          <clm_id>1234567</clm_id>
          <clm_totalCharge>1000.00</clm_totalCharge>
        </subscriberClaim>
</subscriber>' xmlField

Union all 
	select -- '<subscriber><per_first>Jane</per_first><per_last>Doe</per_last></subscriber>' 
'<subscriber>
        <groupNumber>1234</groupNumber>
        <planName>MyPlan</planName>
        <per_last>Doe</per_last>
        <per_first>Jane</per_first>
        <per_ssn>234567890</per_ssn>
        <per_addr1>1 Main St</per_addr1>
        <per_addr2>#8</per_addr2>
        <per_city>New York</per_city>
        <per_state>NY</per_state>
        <per_zip>10465</per_zip>
        <per_dob>19700101</per_dob>
        <per_gender>M</per_gender>
        <subscriberClaim>
          <clm_id>2345678</clm_id>
          <clm_totalCharge>1000.00</clm_totalCharge>
        </subscriberClaim>
</subscriber>'
) tb 


select 
  xmlField.query('./subscriber/per_last').value('.', 'nvarchar(200)') as LastName
, xmlField.query('./subscriber/per_first').value('.', 'nvarchar(200)') as FirstName
, xmlField.query('./subscriber/subscriberClaim/clm_id').value('.', 'nvarchar(200)') as CLM_ID
from #TMP T

Open in new window

CipherISAuthor Commented:
That looks like you are passing in the values.  I want to pull "all" the values like a select statement.

    Select per_first, per_last, clm_id from XML

Open in new window


Is this possible?
G GodwinDatabase AdministratorCommented:
No values are being passed into the query.  The query is selecting from the table using xquery methods.  

In this case, you must know the structure in order to query the xml data.  (If those are the names you are referring to, they are required).

There are no means that I know of where you can query xml data without using xquery, and knowledge of the xml structure.

Assuming the column containing your xml is well defined xml and has the same structure, you could point this query at your actual table (instead of #TMP) and you would see those three fields for all records in your table.  To do this, all you would need to change is the table name and the column name.  

-gg
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

G GodwinDatabase AdministratorCommented:
This:
  Select per_first, per_last, clm_id from XML
is not possible.

you must use xquery methods against XML data.

select 
  [XML_COLUMN].query('./subscriber/per_last').value('.', 'nvarchar(200)') as LastName
, [XML_COLUMN].query('./subscriber/per_first').value('.', 'nvarchar(200)') as FirstName
, [XML_COLUMN].query('./subscriber/subscriberClaim/clm_id').value('.', 'nvarchar(200)') as CLM_ID
from [YOUR_TABLE_NAME] T

Open in new window


for text/varchar/ etc... data:

select 
  CONVERT(XML, [XMLish_COLUMN]).query('./subscriber/per_last').value('.', 'nvarchar(200)') as LastName
, CONVERT(XML, [XMLish_COLUMN]).query('./subscriber/per_first').value('.', 'nvarchar(200)') as FirstName
, CONVERT(XML, [XMLish_COLUMN]).query('./subscriber/subscriberClaim/clm_id').value('.', 'nvarchar(200)') as CLM_ID
from [YOUR_TABLE_NAME] T

Open in new window


Either way, the results would be the same as if you could write the select statement you want to write.  The only thing is, you have to wrap it in the syntax that will allow it to work.
G GodwinDatabase AdministratorCommented:
To clarify, the first code I showed you included the creation of the temp table (#TMP).  That part of the code is not needed for the select statements if you already have a table you are selecting from.  I included it so you could test the whole thing.  Just change the select statement to run against your table and column names.  Then, let me know how that works for you.

-GDG_DBA
CipherISAuthor Commented:
I used your below example.

select 
  CONVERT(XML, [XMLish_COLUMN]).query('./subscriber/per_last').value('.', 'nvarchar(200)') as LastName
, CONVERT(XML, [XMLish_COLUMN]).query('./subscriber/per_first').value('.', 'nvarchar(200)') as FirstName
, CONVERT(XML, [XMLish_COLUMN]).query('./subscriber/subscriberClaim/clm_id').value('.', 'nvarchar(200)') as CLM_ID
from [YOUR_TABLE_NAME] T

Open in new window


I received the results in the pic.

xml query
G GodwinDatabase AdministratorCommented:
What is the name of the table you are querying from?
What is the name of the column that holds the XML data, and what is its type?

-GDG
CipherISAuthor Commented:
The type is text.  
Table - XMLInbound
Field - in_xml
G GodwinDatabase AdministratorCommented:
Is the XML in XMLInbound.[in_xml] the same structure as the sample provided in the question?

Note: the sample is missing the closing tag for subscriber "</subscriber>".
Does the actual data have the closing tags?

If so, this should work:

select 
  convert(xml, in_xml).query('./subscriber/per_last').value('.', 'nvarchar(200)') as LastName
, convert(xml, in_xml).query('./subscriber/per_first').value('.', 'nvarchar(200)') as FirstName
, convert(xml, in_xml).query('./subscriber/subscriberClaim/clm_id').value('.', 'nvarchar(20)') as CLM_ID
from XMLInbound T

Open in new window


-gg
CipherISAuthor Commented:
The below code is what I used.  I copied what you wrote and replace accordingly.    It matches the code I have.  When I run it the values are empty.

select 
  convert(xml, in_xml).query('./subscriber/per_last').value('.', 'nvarchar(200)') as LastName
, convert(xml, in_xml).query('./subscriber/per_first').value('.', 'nvarchar(200)') as FirstName
, convert(xml, in_xml).query('./subscriber/subscriberClaim/clm_id').value('.', 'nvarchar(20)') as CLM_ID
from XMLInbound T

Open in new window


Does the actual data have the closing tags?  Yes, I just copied piece of the XML and not in its entirety
G GodwinDatabase AdministratorCommented:
I just copied piece of the XML and not in its entirety

That's likely the problem.  

Can you share the full xml structure? (take out or obfuscate any actual values).

If you are not getting errors converting to XML, that's great! That means we just need to use the XML schema to address the data you want to read.  

-GDG
CipherISAuthor Commented:
The issue I have is that in the field the whole structure is not displayed.  I've been trying to get it with VARCHAR(MAX) but it still truncates it.
G GodwinDatabase AdministratorCommented:
Let's see how big the data is...

Run this:
Select count(*) from XMLINBOUND

select MIN(DATALENGTH(in_xml)) 'MIN' ,MAX(DATALENGTH(in_xml)) 'MAX' , AVG(DATALENGTH(in_xml)) 'AVG' from XMLINBOUND 

Open in new window

G GodwinDatabase AdministratorCommented:
Let me know the results after you've run those.  Then, I'll show you ways of getting a look at the full XML structure.

Hopefully, it is the same structure for all rows.

-gg
CipherISAuthor Commented:
Will try above.
G GodwinDatabase AdministratorCommented:
Just checking in with you.
CipherISAuthor Commented:
I've tried running the min/max/avg query but it takes too long to run. This is my record count 82461335.  I can't tie up the database that long.  I've tried to use a shorter date range but it is still tying up the database too long.
G GodwinDatabase AdministratorCommented:
O.K.,

Let's  just look at it for a small sample.  

This query will just grab the first 100 rows however the table is indexed.  It should be quite fast.

select MIN(DATALENGTH(in_xml)) 'MIN' ,MAX(DATALENGTH(in_xml)) 'MAX' , AVG(DATALENGTH(in_xml)) 'AVG' 
from (select top 100 in_xml from XMLINBOUND ) t1

Open in new window

CipherISAuthor Commented:
MIN 823      
MAX 9057      
AVG 1385
G GodwinDatabase AdministratorCommented:
Alright,  now to see the whole XML structure for a record (the first one, the largest one, the smallest one...)  you will want to select the contents.  However switch to 'results to text' mode first (CTRL+T in SSMS).  

In text mode, the output should all be able to display in your result screen.  If you want to see it in a text file, you can send the results to a file instead (ctrl+shift+f).  

Then, run the select

--press ctrl+t
select top 1 in_xml from XMLINBOUND 

or 
select top 1 in_xml from XMLINBOUND 
where ... add your own filter to find a specific record you want to look at (do this one at a time).

Open in new window



This will allow you to see the full xml structure.

Once you can see the full structure, you should be able to find the nodes you need. If you need help with parsing the xml, I will need to see the structure (without real data please).

-GDG
G GodwinDatabase AdministratorCommented:
You may need to change your query output settings to see all of the data.  

like:
SSMS-Query-Ouput-Options-1.JPG
or
SSMS-Query-Ouput-Options-2.JPG
Also, if the column is actually XML datatype (or you convert it to xml in your select) you should see it underlined in the grid result set. If it is, you can click on it and it will open the XML in a new tab (it should show you the whole thing.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CipherISAuthor Commented:
How do I turn off the Ctrl+T?
G GodwinDatabase AdministratorCommented:
Ctrl+D
CipherISAuthor Commented:
Still cutting it off.
G GodwinDatabase AdministratorCommented:
O.K.

Just for grins try this.
--press ctrl+d
select top 1 convert(xml, in_xml) as in_xml_xml from XMLINBOUND

Open in new window


You will either get an error (if the data can't be converted to xml) or you will get an xml column in the result set.
If you click on it, it should open another tab with the full xml contents.

Failing that, let's send it to a file.  

--press ctrl+shift-f
select top 1 in_xml from XMLINBOUND

Open in new window

A window will pop up asking you for a file name to write the output to.  Enter one. Then, you should be able to see the full result in the file.

-gg
CipherISAuthor Commented:
Finally figured out how to get the xml.  When the data displays on the grid.  Click on the column.  Right click then save as text.
CipherISAuthor Commented:
Thanks for your help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.