querying Infopath data- xml column in sql 2005

dev123 · Feb 5, 2008

Hi
I have created a simple Infopath 2003 form and saved the data to sql server
2005 xml column in express edtion. It stored to IPForms table somthing like
this
-----------------------------------------------------------------------------------------
<?mso-infoPathSolution solutionVersion="1.0.0.11" productVersion="11.0.8165"
PIVersion="1.0.0.0" href="file:///C:\InfoPath_Work\SubmitXMLtoDB.xsn"
name="urn:schemas-microsoft-com

ffice:infopath:SubmitXMLtoDB:-myXSD-2007-11-28T19-48-30" ?>
<?mso-application progid="InfoPath.Document"?>
<?mso-infoPath-file-attachment-present?>
<my:TestFields xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xhtml="http://www.w3.org/1999/xhtml"

xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-
11-28T19:48:30"
xmlns:xd="http://schemas.microsoft.com/office/infopath/2003"
xml:lang="en-
us">
<my:qryFields />
<my

ataFileds>
<my:id>2</my:id>
<my:EName>
<my:LName>LName</my:LName>
<my:FName>FName</my:FName>
<my:Image>/9j/4A</my:Image>
</my:EName>
<my:Gendar>1</my:Gendar>
<my:FColor>
<my:Red>false</my:Red>
<my:Blue>false</my:Blue>
<my:Green>true</my:Green>
</my:FColor>
<my

tPicker>2008-01-31</my

tPicker>
<my:txtBox>testtest</my:txtBox>
<my:txtrtf>rich text</my:txtrtf>
<my:File>x0lGQRQAAA</my:File>
</my

ataFileds>
</my:TestFields>
----------------------------------------------------------------
Now if I query the IPForms table like

SELECT Form.value('(/TestFields/DataFileds/@LName)[1]', 'varchar(50)' )
FROM IPForms

SELECT Form.query('(/TestFields/DataFileds/LName)')
FROM IPForms

I am getting NULL for the first and blank for the second query.
Can some body help me where I am going wrong?
Thank you

Clay Fox · Feb 6, 2008

I guess I am a bit confused.

What are you trying to accomplish?

How are you saving or submitting the XML to your SQL table?

Where are you doing these select queries? in another InfoPath form?

Give me an overview of what you are trying to accomplish and how you are
going about it.

dev123 · Feb 6, 2008

Hi Clay
I have creaded a form and, using a webservice and infopath parameter option
"Entire Form(xml document,Including processing instructions)" passed it to
webmethod as xmlelement. In the webservice, taking the innerXml of the
element ,inserted into sqlserver 2005 table column with xml datatype .
It is being inserted to table as I showed in my question

Now I am trying to query that xml column in Sql server 2005 to find a
element value using
SELECT xmlColName.value('(/TestFields/DataFileds/EName/@LName)[1]',
'varchar(50)' )
FROM IPForms

This query I am trying in Sql Server Management Studio.

Thank you

Clay Fox said:
I guess I am a bit confused.

What are you trying to accomplish?

How are you saving or submitting the XML to your SQL table?

Where are you doing these select queries? in another InfoPath form?

Give me an overview of what you are trying to accomplish and how you are
going about it.

--
Clay Fox / Microsoft InfoPath MVP
www.InfoPathDev.com / The InfoPath Authority / Downloads, Samples, How-To,
Experts, Forum

dev123 said:

Hi
I have created a simple Infopath 2003 form and saved the data to sql server
2005 xml column in express edtion. It stored to IPForms table somthing like
this
-----------------------------------------------------------------------------------------
<?mso-infoPathSolution solutionVersion="1.0.0.11" productVersion="11.0.8165"
PIVersion="1.0.0.0" href="file:///C:\InfoPath_Work\SubmitXMLtoDB.xsn"
name="urn:schemas-microsoft-comffice:infopath:SubmitXMLtoDB:-myXSD-2007-11-28T19-48-30" ?>
<?mso-application progid="InfoPath.Document"?>
<?mso-infoPath-file-attachment-present?>
<my:TestFields xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xhtml="http://www.w3.org/1999/xhtml"

xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-
11-28T19:48:30"
xmlns:xd="http://schemas.microsoft.com/office/infopath/2003"
xml:lang="en-
us">
<my:qryFields />
<myataFileds>
<my:id>2</my:id>
<my:EName>
<my:LName>LName</my:LName>
<my:FName>FName</my:FName>
<my:Image>/9j/4A</my:Image>
</my:EName>
<my:Gendar>1</my:Gendar>
<my:FColor>
<my:Red>false</my:Red>
<my:Blue>false</my:Blue>
<my:Green>true</my:Green>
</my:FColor>
<mytPicker>2008-01-31</mytPicker>
<my:txtBox>testtest</my:txtBox>
<my:txtrtf>rich text</my:txtrtf>
<my:File>x0lGQRQAAA</my:File>
</myataFileds>
</my:TestFields>
----------------------------------------------------------------
Now if I query the IPForms table like

SELECT Form.value('(/TestFields/DataFileds/@LName)[1]', 'varchar(50)' )
FROM IPForms

SELECT Form.query('(/TestFields/DataFileds/LName)')
FROM IPForms

I am getting NULL for the first and blank for the second query.
Can some body help me where I am going wrong?
Thank you

Click to expand...

Clay Fox · Feb 6, 2008

Ok got it.

I would think the syntax would be.

Select * From IPForms Where columnname contains 'text'

You could also use the Where columnname like '%text%'

This would return any rows which had the text specified in the text of the
column field.

You may want to investigate the Database Accelerator package from
http://www.qdabra.com
It is an installable package of web services which will let you dynamically
query and submit to your SQl databse as well as stores the forms themselves
in SQL similar to what you are doing.

--
Clay Fox / Microsoft InfoPath MVP
www.InfoPathDev.com / The InfoPath Authority / Downloads, Samples, How-To,
Experts, Forum

dev123 said:
Hi Clay
I have creaded a form and, using a webservice and infopath parameter option
"Entire Form(xml document,Including processing instructions)" passed it to
webmethod as xmlelement. In the webservice, taking the innerXml of the
element ,inserted into sqlserver 2005 table column with xml datatype .
It is being inserted to table as I showed in my question

Now I am trying to query that xml column in Sql server 2005 to find a
element value using
SELECT xmlColName.value('(/TestFields/DataFileds/EName/@LName)[1]',
'varchar(50)' )
FROM IPForms

This query I am trying in Sql Server Management Studio.

Thank you

Clay Fox said:

I guess I am a bit confused.

What are you trying to accomplish?

How are you saving or submitting the XML to your SQL table?

Where are you doing these select queries? in another InfoPath form?

Give me an overview of what you are trying to accomplish and how you are
going about it.

--
Clay Fox / Microsoft InfoPath MVP
www.InfoPathDev.com / The InfoPath Authority / Downloads, Samples, How-To,
Experts, Forum

dev123 said:

Hi
I have created a simple Infopath 2003 form and saved the data to sql server
2005 xml column in express edtion. It stored to IPForms table somthing like
this
-----------------------------------------------------------------------------------------
<?mso-infoPathSolution solutionVersion="1.0.0.11" productVersion="11.0.8165"
PIVersion="1.0.0.0" href="file:///C:\InfoPath_Work\SubmitXMLtoDB.xsn"
name="urn:schemas-microsoft-comffice:infopath:SubmitXMLtoDB:-myXSD-2007-11-28T19-48-30" ?>
<?mso-application progid="InfoPath.Document"?>
<?mso-infoPath-file-attachment-present?>
<my:TestFields xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xhtml="http://www.w3.org/1999/xhtml"

xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-
11-28T19:48:30"
xmlns:xd="http://schemas.microsoft.com/office/infopath/2003"
xml:lang="en-
us">
<my:qryFields />
<myataFileds>
<my:id>2</my:id>
<my:EName>
<my:LName>LName</my:LName>
<my:FName>FName</my:FName>
<my:Image>/9j/4A</my:Image>
</my:EName>
<my:Gendar>1</my:Gendar>
<my:FColor>
<my:Red>false</my:Red>
<my:Blue>false</my:Blue>
<my:Green>true</my:Green>
</my:FColor>
<mytPicker>2008-01-31</mytPicker>
<my:txtBox>testtest</my:txtBox>
<my:txtrtf>rich text</my:txtrtf>
<my:File>x0lGQRQAAA</my:File>
</myataFileds>
</my:TestFields>
----------------------------------------------------------------
Now if I query the IPForms table like

SELECT Form.value('(/TestFields/DataFileds/@LName)[1]', 'varchar(50)' )
FROM IPForms

SELECT Form.query('(/TestFields/DataFileds/LName)')
FROM IPForms

I am getting NULL for the first and blank for the second query.
Can some body help me where I am going wrong?
Thank you

Click to expand...

Click to expand...

S.Y.M. Wong-A-Ton · Feb 7, 2008

Both XPath expressions are returning nodes that cannot be found, probably
because of a missing namespace and incorrect XPath expressions.

Try this:

WITH XMLNAMESPACES(
'http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-11-28T19:48:30'
AS "my")
SELECT Form.value('(/my:TestFields/my

ataFileds/my:EName/my:LName)[1]',
'varchar(50)' )
FROM IPForms

querying Infopath data- xml column in sql 2005

dev123

Clay Fox

dev123

Clay Fox

S.Y.M. Wong-A-Ton