alleybye 6 Newbie Poster

Hi, I have a problem on getting data from XML file. I am honestly have 0 knowledge on this. Here is the XML data content, i opened the XML file using notepad then pasted here.

<?xml version="1.0" encoding="utf-8"?>
<?mso-infoPathSolution name="urn:schemas-microsoft-com:office:infopath:Test-Form:-myXSD-2012-09-02T13-13-23" solutionVersion="1.0.0.55" productVersion="14.0.0.0" PIVersion="1.0.0.0" href="http://share.test.com/it/Test%20Form/Forms/template.xsn"?>
<?mso-application progid="InfoPath.Document" versionProgid="InfoPath.Document.3"?>
<my:myFields xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:pc="http://schemas.microsoft.com/office/infopath/2007/PartnerControls" xmlns:ma="http://schemas.microsoft.com/office/2009/metadata/properties/metaAttributes" xmlns:d="http://schemas.microsoft.com/office/infopath/2009/WSSList/dataFields" xmlns:q="http://schemas.microsoft.com/office/infopath/2009/WSSList/queryFields" xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" xmlns:dms="http://schemas.microsoft.com/office/2009/documentManagement/types" xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2012-09-02T13:13:23" xmlns:xd="http://schemas.microsoft.com/office/infopath/2003" xml:lang="en-US">
    <my:group1>
        <my:txtFirstName>TEST1</my:txtFirstName>
        <my:txtLastName>TEST2</my:txtLastName>
        <my:Skills>Chess</my:Skills><my:Skills>Piko</my:Skills><my:Skills>Tumbang preso</my:Skills>
    </my:group1>
</my:myFields>

And here is my sql query

DECLARE @foo XML
SET @foo =
  '<my:myFields xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:pc="http://schemas.microsoft.com/office/infopath/2007/PartnerControls" xmlns:ma="http://schemas.microsoft.com/office/2009/metadata/properties/metaAttributes" xmlns:d="http://schemas.microsoft.com/office/infopath/2009/WSSList/dataFields" xmlns:q="http://schemas.microsoft.com/office/infopath/2009/WSSList/queryFields" xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" xmlns:dms="http://schemas.microsoft.com/office/2009/documentManagement/types" xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2012-09-02T13:13:23" xmlns:xd="http://schemas.microsoft.com/office/infopath/2003" xml:lang="en-US">
    <my:group1>
        <my:txtFirstName>Test Name</my:txtFirstName>
        <my:txtLastName>Test Lastname</my:txtLastName>
        <my:Skills>Chess</my:Skills><my:Skills>Piko</my:Skills><my:Skills>Tumbang preso</my:Skills>
    </my:group1>
</my:myFields>'

SELECT
        x.value('(my:txtFirstName/text())[1]', 'nvarchar(max)') as FirstName,
        x.value('(my:txtLastName/text())[1]', 'nvarchar(max)') as LastName
FROM
        @foo.nodes('/my:myFields/my:group1') abc(x)

And it gives me this error

XQuery [nodes()]: The name "my" does not denote a namespace.

Please help me, i really dont know how to use namespace. THANKS IN ADVANCE!!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.