how to query to xml and return it as table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to query to xml and return it as table

Author

Topic

hdv212
Posting Yak Master

140 Posts

Posted - 2010-09-16 : 13:09:39

Hi
i'm working on an application which stored infopath forms in database as xml.
now i want to get query of infopath forms that each element convert to column. here is my sample xml form :

<?mso-infoPathSolution solutionVersion="1.0.0.29" productVersion="12.0.0" PIVersion="1.0.0.0" href="file:///D:\customForm3.xsn" name="urn:schemas-microsoft-com:office:infopath:customForm3:-myXSD-2010-09-11T10-56-47" ?>
<?mso-application progid="InfoPath.Document" versionProgid="InfoPath.Document.2"?>
<my:myFields 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/2010-09-11T10:56:47" xmlns:xd="http://schemas.microsoft.com/office/infopath/2003" xml:lang="en-us">
<my:txtFirstName>test1</my:txtFirstName>
<my:txtLastName>test2</my:txtLastName>
<my:txtFatherName>test3</my:txtFatherName>
<my:txtNationalCode>8278273828</my:txtNationalCode>
<my:cmbReuqesType>2</my:cmbReuqesType>
<my:dtmRequestDate>2010-09-17</my:dtmRequestDate>
<my:txtDescription>test4</my:txtDescription>
</my:myFields>

now, i want to get data which returns a table like this :

FormName txtFirstName txtFatherName txtFatherName txtNationalCode cmbReuqesType dtmRequestDate txtDescription
form1 test1 test2 test3 8278273828 2 2010-09-17 test4

i know which i must using xmlColumn.Value but i don't know how to accomplish this.
can anybody help me how to do this ?
thanks in advance

hdv212
Posting Yak Master

140 Posts

Posted - 2010-09-16 : 14:39:26

Hi again
after a lot of efforts, i've used this query expression sample to select FirstName node :

select formname,
FormContent.value('(/myFields/txtFirstName)[1]','varchar(50)') as FirstName
from dbo.FormData

but in the result set, column FirstName has null value whereas it contains data.
where is my problem and how to solve it ?
thanks in advance

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-09-16 : 14:41:12

Run this ...

declare @temp xml
set @temp =
'
<?mso-infoPathSolution solutionVersion="1.0.0.29" productVersion="12.0.0" PIVersion="1.0.0.0" href="file:///D:\customForm3.xsn" name="urn:schemas-microsoft-com:office:infopath:customForm3:-myXSD-2010-09-11T10-56-47" ?>
<?mso-application progid="InfoPath.Document" versionProgid="InfoPath.Document.2"?>
<my:myFields 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/2010-09-11T10:56:47" xmlns:xd="http://schemas.microsoft.com/office/infopath/2003" xml:lang="en-us">
<my:txtFirstName>test1</my:txtFirstName>
<my:txtLastName>test2</my:txtLastName>
<my:txtFatherName>test3</my:txtFatherName>
<my:txtNationalCode>8278273828</my:txtNationalCode>
<my:cmbReuqesType>2</my:cmbReuqesType>
<my:dtmRequestDate>2010-09-17</my:dtmRequestDate>
<my:txtDescription>test4</my:txtDescription>
</my:myFields>'

select @temp

;with xmlnamespaces('http://schemas.microsoft.com/office/infopath/2003/myXSD/2010-09-11T10:56:47' as Q)
select 'form1' as FormName,
t.c.value('(Q:txtFirstName[1]/text()[1])','varchar(100)') as FirstName,
t.c.value('(Q:txtLastName[1]/text()[1])','varchar(100)') as LastName,
t.c.value('(Q:txtFatherName[1]/text()[1])','varchar(100)') as PatherName,
t.c.value('(Q:txtNationalCode[1]/text()[1])','varchar(100)') as NationalCode,
t.c.value('(Q:cmbReuqesType[1]/text()[1])','varchar(100)') as ReuqesType,
t.c.value('(Q:dtmRequestDate[1]/text()[1])','varchar(100)') as RequestDate,
t.c.value('(Q:txtDescription[1]/text()[1])','varchar(100)') as Description
from @temp.nodes('/Q:myFields') t(c)

-- expected result --
FormName FirstName LastName PatherName NationalCode ReuqesType RequestDate Description
form1 test1 test2 test3 8278273828 2 2010-09-17 test4

hdv212
Posting Yak Master

140 Posts

Posted - 2010-09-16 : 16:07:09

Thanks namman
but i donn't understand how to customize this query. also it does not appropriate for me, because FormName is my another sql table column (and is seperate of xml content). can u give me more information about how to and make this query ?
thanks in advance

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-09-16 : 16:41:22

quote:

also it does not appropriate for me, because FormName is my another sql table column (and is seperate of xml content).

Could you provide table structure and sample data?

If the FormName column is the same table with xml document, then try this

declare @yourTable table(form varchar(10), x xml)
declare @temp xml
set @temp =
'
<?mso-infoPathSolution solutionVersion="1.0.0.29" productVersion="12.0.0" PIVersion="1.0.0.0" href="file:///D:\customForm3.xsn" name="urn:schemas-microsoft-com:office:infopath:customForm3:-myXSD-2010-09-11T10-56-47" ?>
<?mso-application progid="InfoPath.Document" versionProgid="InfoPath.Document.2"?>
<my:myFields 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/2010-09-11T10:56:47" xmlns:xd="http://schemas.microsoft.com/office/infopath/2003" xml:lang="en-us">
<my:txtFirstName>test1</my:txtFirstName>
<my:txtLastName>test2</my:txtLastName>
<my:txtFatherName>test3</my:txtFatherName>
<my:txtNationalCode>8278273828</my:txtNationalCode>
<my:cmbReuqesType>2</my:cmbReuqesType>
<my:dtmRequestDate>2010-09-17</my:dtmRequestDate>
<my:txtDescription>test4</my:txtDescription>
</my:myFields>'
insert into @yourTable values('Form 1', @temp);
set @temp =
'
<?mso-infoPathSolution solutionVersion="1.0.0.29" productVersion="12.0.0" PIVersion="1.0.0.0" href="file:///D:\customForm3.xsn" name="urn:schemas-microsoft-com:office:infopath:customForm3:-myXSD-2010-09-11T10-56-47" ?>
<?mso-application progid="InfoPath.Document" versionProgid="InfoPath.Document.2"?>
<my:myFields 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/2010-09-11T10:56:47" xmlns:xd="http://schemas.microsoft.com/office/infopath/2003" xml:lang="en-us">
<my:txtFirstName>John</my:txtFirstName>
<my:txtLastName>Smith</my:txtLastName>
<my:txtFatherName>Washington</my:txtFatherName>
<my:txtNationalCode>1111111</my:txtNationalCode>
<my:cmbReuqesType>25</my:cmbReuqesType>
<my:dtmRequestDate>2010-09-20</my:dtmRequestDate>
<my:txtDescription>Something...</my:txtDescription>
</my:myFields>'
insert into @yourTable values('Form 2', @temp);

select @temp

;with xmlnamespaces('http://schemas.microsoft.com/office/infopath/2003/myXSD/2010-09-11T10:56:47' as Q)
select form as FormName,
t.c.value('(Q:txtFirstName[1]/text()[1])','varchar(100)') as FirstName,
t.c.value('(Q:txtLastName[1]/text()[1])','varchar(100)') as LastName,
t.c.value('(Q:txtFatherName[1]/text()[1])','varchar(100)') as PatherName,
t.c.value('(Q:txtNationalCode[1]/text()[1])','varchar(100)') as NationalCode,
t.c.value('(Q:cmbReuqesType[1]/text()[1])','varchar(100)') as ReuqesType,
t.c.value('(Q:dtmRequestDate[1]/text()[1])','varchar(100)') as RequestDate,
t.c.value('(Q:txtDescription[1]/text()[1])','varchar(100)') as Description
from @yourTable cross apply x.nodes('/Q:myFields') t(c)

-- result --
FormName FirstName LastName PatherName NationalCode ReuqesType RequestDate Description
Form 1 test1 test2 test3 8278273828 2 2010-09-17 test4
Form 2 John Smith Washington 1111111 25 2010-09-20 Something...

hdv212
Posting Yak Master

140 Posts

Posted - 2010-09-16 : 17:04:43

thanks namman
i've used this query and get correct result :

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/office/infopath/2003/myXSD/2010-09-11T10:56:47')
SELECT
FormName,
FormContent.value('(/myFields/txtFirstName)[1]','varchar(50)') AS FirstName,
FormContent.value('(/myFields/txtLastName)[1]','varchar(50)') AS LastName,
FormContent.value('(/myFields/txtFatherName)[1]','varchar(50)') AS FatherName,
FormContent.value('(/myFields/txtNationalCode)[1]','varchar(50)') AS NationalCode,
FormContent.value('(/myFields/cmbReuqesType)[1]','int') AS RequestType,
FormContent.value('(/myFields/dtmRequestDate)[1]','datetime') AS RequestDate,
FormContent.value('(/myFields/txtDescription)[1]','nvarchar(50)') AS Description
FROM FormData

but i have a little question :

why use [1] in last node ?
thanks again

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-09-16 : 19:38:26

quote:

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/office/infopath/2003/myXSD/2010-09-11T10:56:47')
SELECT
FormName,
FormContent.value('(/myFields/txtFirstName)[1]','varchar(50)') AS FirstName,
FormContent.value('(/myFields/txtLastName)[1]','varchar(50)') AS LastName,
FormContent.value('(/myFields/txtFatherName)[1]','varchar(50)') AS FatherName,
FormContent.value('(/myFields/txtNationalCode)[1]','varchar(50)') AS NationalCode,
FormContent.value('(/myFields/cmbReuqesType)[1]','int') AS RequestType,
FormContent.value('(/myFields/dtmRequestDate)[1]','datetime') AS RequestDate,
FormContent.value('(/myFields/txtDescription)[1]','nvarchar(50)') AS Description
FROM FormData

but i have a little question :

why use [1] in last node ?

It is the order of the node for the same element and level. If you want to get the next, you should make it [2].

The query is general format for accessing elements. If you use attribute, format is different.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources