Just when I thought I had a handle on this.  Given the XML file below, I am
trying to shread and obtain all FileName nodes in UpgradeScriptFiles and
RollBackScriptFiles in DBDeployment

In each case I only get the first node i.e.
SQL_510_20090520_Test1.sql and RBK_510_20090520_Test1.sql respectively

What am I missing?

Thanks for any light that can be shead!

RJ



Declare    @DeploymentControlXML XML

<<<code that sets @DeploymentControlXML>>>

Select    x.Item.value('FileName[1]', 'VARCHAR(100)')
FROM    @DeploymentControlXML.nodes('//DBDeployment/UpgradeScriptFiles') As
x(item)

--Insert    @DeploymentRollBackFiles (ScriptFileName)
Select    x.Item.value('FileName[1]', 'VARCHAR(100)')
FROM    @DeploymentControlXML.nodes('//DBDeployment/RollBackScriptFiles') As
x(item)

<DBDeployment>
    <Version>5.0.2</Version>
    <BuildId>1357</BuildId>
    <BuildDate>2009-05-29</BuildDate>
    <BaseVersion>5.0.0</BaseVersion>
    <UpgradeScriptFiles>
        <FileName>SQL_510_20090520_Test1.sql</FileName>
        <FileName>SQL_510_20090520_Test2.sql</FileName>
        <FileName>SQL_510_20090520_Test3.sql</FileName>
        <FileName>SQL_510_20090520_Test4.sql</FileName>
    </UpgradeScriptFiles>
    <RollBackScriptFiles>
        <FileName>RBK_510_20090520_Test1.sql</FileName>
        <FileName>RBK_510_20090520_Test2.sql</FileName>      
    </RollBackScriptFiles>
</DBDeployment>

RJ wrote:

You need to select those FileName elements e.g.

DECLARE @DeploymentControlXML XML;

SET @DeploymentControlXML = N'<DBDeployment>
    <Version>5.0.2</Version>
    <BuildId>1357</BuildId>
    <BuildDate>2009-05-29</BuildDate>
    <BaseVersion>5.0.0</BaseVersion>
    <UpgradeScriptFiles>
        <FileName>SQL_510_20090520_Test1.sql</FileName>
        <FileName>SQL_510_20090520_Test2.sql</FileName>
        <FileName>SQL_510_20090520_Test3.sql</FileName>
        <FileName>SQL_510_20090520_Test4.sql</FileName>
    </UpgradeScriptFiles>
    <RollBackScriptFiles>
        <FileName>RBK_510_20090520_Test1.sql</FileName>
        <FileName>RBK_510_20090520_Test2.sql</FileName>   
    </RollBackScriptFiles>
</DBDeployment>';


SELECT
   x.item.value('.', 'NVARCHAR(100)') AS FileName
FROM

@DeploymentControlXML.nodes('/DBDeployment/RollBackScriptFiles/FileName')
AS x(item);

Your solution worked and I thank you for that.  Still not sure of the
difference in methodologies though.  I'll play around with it work it out.

Thanks again.





Show quoteHide quote

RJ wrote:
The XPath expression you pass to the nodes method determines which nodes
are mapped to rows. You passed in
   //DBDeployment/RollBackScriptFiles
so you got one row for each RollBackScriptFiles element and as there is
only one such element you got one row.

My suggestion


uses
   /DBDeployment/RollBackScriptFiles/FileName
so you get one row for each FileName element resulting in two rows with
the sample XML you have.

Thanks for the clarification; that helped!

Show quoteHide quote

Obtain Data from XML column
Bulk load and XSD problem...
Obtaining data from an XML column to a document
changing the results header with for xml
Exporting to an XML file
missing elements while passing XML from .NET to SQL 2005
SQLXML XPath data with apostrophe
Need help w/ importing data via web services
pons and cons of xml in sql server, which way is better xml or reational table?
Use of WITH XMLNAMESPACES