SQL Query XML Help ...... - SQL Server Forums

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 2008 Forums

SQL Server Administration (2008)

SQL Query XML Help ......

Author

Topic

sqldba20
Posting Yak Master

183 Posts

Posted - 2012-02-21 : 12:26:36

Folks:

I need help with a SQL Script to shred my XML data which is stored in a SQL Table. The XMLData is in this format. Here is one of the record.

<?xml version="1.0" encoding="utf-16"?>
<?mso-infoPathSolution name="urn:schemas-microsoft-com:office:infopath:Annual-Cert:-myXSD-2011-12-28T21-32-27" solutionVersion="1.0.0.105" productVersion="12.0.0.0" PIVersion="1.0.0.0" href="http://ABC/MCSClarify/Cert/Annual/Forms/template.xsn"?>
<?mso-application progid="InfoPath.Document" versionProgid="InfoPath.document.2"?>
<my:myFields xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2011-12-28T21:32:27" xmlns:xd="http://schemas.microsoft.com/office/infopath/2003" xml:lang="en-US">
      <my:field1>CERT FORM</my:field1>
      <my:field2 />
      <my:STList />
      <my:Chk_listYes>true</my:Chk_listYes>
      <my:Chk_listNo>false</my:Chk_listNo>
      <my:field6 />
      <my:Chk_familymembernone>true</my:Chk_familymembernone>
</my:myFields>

Thanks !

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-21 : 12:57:25

Something like this:

;WITH XMLNAMESPACES ( default 'http://schemas.microsoft.com/office/infopath/2003/myXSD/2011-12-28T21:32:27')
SELECT
	c.value('field1[1]','varchar(100)'),
	c.value('field2[1]','varchar(100)')
	--, other nodes here
FROM
	YourTable y
	CROSS APPLY yourXmlCol.nodes('//myFields') t(c);
	@x.nodes('//myFields') t(c);

sqldba20
Posting Yak Master

183 Posts

Posted - 2012-02-21 : 13:23:57

What will be in the @x ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-21 : 14:05:16

quote:
Originally posted by sqldba20

What will be in the @x ?

@x is actually variable that contains XML

where is XML stored now?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqldba20
Posting Yak Master

183 Posts

Posted - 2012-02-21 : 15:18:38

It is stored in a table.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-21 : 15:27:56

My bad sqldba and Visakh. That last line should not even be there. Copy/paste error, I guess.

;WITH XMLNAMESPACES ( default 'http://schemas.microsoft.com/office/infopath/2003/myXSD/2011-12-28T21:32:27')
SELECT
	c.value('field1[1]','varchar(100)'),
	c.value('field2[1]','varchar(100)')
	--, other nodes here
FROM
	YourTable y
	CROSS APPLY yourXmlCol.nodes('//myFields') t(c);

sqldba20
Posting Yak Master

183 Posts

Posted - 2012-02-21 : 16:51:20

Thanks for your help !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources