locked
Shred XML Data from Table RRS feed

  • Question

  • 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 !

    Tuesday, February 21, 2012 6:08 PM

Answers

  • Try this:

    DECLARE @yourTable TABLE ( rowId INT IDENTITY PRIMARY KEY, yourXML XML )
     
    INSERT INTO @yourTable ( yourXML )
    VALUES ( N'<?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>' )
    
    ;WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/office/infopath/2003/myXSD/2011-12-28T21:32:27' AS my )
    SELECT
    	t.rowId,
    	f.c.value('(my:field1/text())[1]', 'VARCHAR(MAX)') AS field1,
    	f.c.value('(my:field2/text())[1]', 'VARCHAR(MAX)') AS field2,
    	f.c.value('(my:STList/text())[1]', 'VARCHAR(MAX)') AS STList,
    	f.c.value('(my:STList/text())[1]', 'VARCHAR(MAX)') AS STList,
    	f.c.value('(my:Chk_listYes/text())[1]', 'VARCHAR(MAX)') AS Chk_listYes,
    	f.c.value('(my:Chk_listNo/text())[1]', 'VARCHAR(MAX)') AS Chk_listNo,
    	f.c.value('(my:field6/text())[1]', 'VARCHAR(MAX)') AS field6,
    	f.c.value('(my:Chk_familymembernone/text())[1]', 'VARCHAR(MAX)') AS Chk_familymembernone
    	
    FROM @yourTable t
    	CROSS APPLY t.yourXML.nodes('my:myFields') f(c)

    As the SQL Server XML datatype does not store the encoding your field might not be using it, is it VARCHAR(MAX) for example?  If so, cast the data to XML before using nodes against it.
    • Marked as answer by sqldba20 Wednesday, February 22, 2012 2:32 PM
    Tuesday, February 21, 2012 9:58 PM
    Answerer

All replies

  • Nevermind... I got the SQL query.

    Thanks !

    Tuesday, February 21, 2012 9:52 PM
  • Due  to the unicode encoding I expect the data are inserted as unicode as per the following example.

    In that case, the snippet below should help you to get startet.

    Otherwise, please post the data as you use it using a similar INSER SELET statement.

    DECLARE @tbl TABLE
    (
     id INT,
     col1 XML   
    )
    INSERT INTO @tbl
    VALUES (1,N'<?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>'),
    (2,N'<?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 FORM2</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>');
    
    WITH xmlnamespaces ('http://schemas.microsoft.com/office/infopath/2003/myXSD/2011-12-28T21:32:27' AS my,
    'http://schemas.microsoft.com/office/infopath/2003' AS xd,
    'http://www.w3.org/2001/XMLSchema-instance' AS xsi)
    SELECT 
    c.value('my:field1[1]','varchar(30)') as field1,
    c.value('my:Chk_listYes[1]','varchar(30)') as Chk_listYes,
    c.value('my:Chk_listNo[1]','varchar(30)') as Chk_listNo
    FROM  @tbl t1
    CROSS APPLY col1.nodes('my:myFields')T(c)

    Tuesday, February 21, 2012 9:58 PM
  • Try this:

    DECLARE @yourTable TABLE ( rowId INT IDENTITY PRIMARY KEY, yourXML XML )
     
    INSERT INTO @yourTable ( yourXML )
    VALUES ( N'<?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>' )
    
    ;WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/office/infopath/2003/myXSD/2011-12-28T21:32:27' AS my )
    SELECT
    	t.rowId,
    	f.c.value('(my:field1/text())[1]', 'VARCHAR(MAX)') AS field1,
    	f.c.value('(my:field2/text())[1]', 'VARCHAR(MAX)') AS field2,
    	f.c.value('(my:STList/text())[1]', 'VARCHAR(MAX)') AS STList,
    	f.c.value('(my:STList/text())[1]', 'VARCHAR(MAX)') AS STList,
    	f.c.value('(my:Chk_listYes/text())[1]', 'VARCHAR(MAX)') AS Chk_listYes,
    	f.c.value('(my:Chk_listNo/text())[1]', 'VARCHAR(MAX)') AS Chk_listNo,
    	f.c.value('(my:field6/text())[1]', 'VARCHAR(MAX)') AS field6,
    	f.c.value('(my:Chk_familymembernone/text())[1]', 'VARCHAR(MAX)') AS Chk_familymembernone
    	
    FROM @yourTable t
    	CROSS APPLY t.yourXML.nodes('my:myFields') f(c)

    As the SQL Server XML datatype does not store the encoding your field might not be using it, is it VARCHAR(MAX) for example?  If so, cast the data to XML before using nodes against it.
    • Marked as answer by sqldba20 Wednesday, February 22, 2012 2:32 PM
    Tuesday, February 21, 2012 9:58 PM
    Answerer