querying Infopath data- xml column in sql 2005

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dev123 - 05 Feb 2008 21:33 GMT

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:office: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:DataFileds>
<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:DtPicker>2008-01-31</my:DtPicker>
<my:txtBox>testtest</my:txtBox>
<my:txtrtf>rich text</my:txtrtf>
<my:File>x0lGQRQAAA</my:File>
</my:DataFileds>
</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

Reply to this Message

Clay Fox - 06 Feb 2008 16:37 GMT

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.

Signature

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

> Hi
> I have created a simple Infopath 2003 form and saved the data to sql server
[quoted text clipped - 46 lines]
> Can some body help me where I am going wrong?
> Thank you

Reply to this Message

dev123 - 06 Feb 2008 18:54 GMT

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

> I guess I am a bit confused.
>
[quoted text clipped - 57 lines]
> > Can some body help me where I am going wrong?
> > Thank you

Reply to this Message

Clay Fox - 06 Feb 2008 19:03 GMT

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.

Signature

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

> Hi Clay
> I have creaded a form and, using a webservice and infopath parameter option
[quoted text clipped - 74 lines]
> > > Can some body help me where I am going wrong?
> > > Thank you

Reply to this Message

S.Y.M. Wong-A-Ton - 07 Feb 2008 07:28 GMT

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:DataFileds/my:EName/my:LName)[1]',
'varchar(50)' )
FROM IPForms

---
S.Y.M. Wong-A-Ton

> Hi
> I have created a simple Infopath 2003 form and saved the data to sql server
[quoted text clipped - 46 lines]
> Can some body help me where I am going wrong?
> Thank you

Reply to this Message