XML query - 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 2005 Forums

Transact-SQL (2005)

XML query

Author

Topic

Leecau
Starting Member

5 Posts

Posted - 2009-08-18 : 15:12:21

I have a large XML file and need to get some of the information together into a table so I can manipulate it in the database.
The XML is as follows (Sorry if it is too long but thought it best to include the whole lot)

Declare @p3 XML

set @p3 =
'<ProductRange xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance " xmlns:xsd="http://www.w3.org/2001/XMLSchema " xmlns="http://schemas.i.com/2008/01/product " Id="C1B64218-8B17-11DE-AE24-
5D1056D89593" Name="SLoanProduceRangeLEE" Brand="Splus" ProductType="LOAN">
<StartDate>2008-08-01T00:00:00</StartDate>
<EndDate>2020-12-31T00:00:00</EndDate>
<Status>Live</Status>
<Description>A desc product LEE</Description>
<Currency>GBP</Currency>
<AvailableRule />
<Criteria />
<ProductClassGroups />
<ProductClasses>
    <ProductClass Id="D1353AE6-8B17-11DE-817C-E51056D89593" Name="SLoanClassLEE">
      <StartDate>2008-08-01T00:00:00</StartDate>
      <EndDate>2020-12-31T00:00:00</EndDate>
      <Status>Live</Status>
      <Description>A test product class</Description>
      <Code>NC004LEE</Code>
      <Order>1</Order>
      <AvailableRule>CoreLoanApply_CheckProductClassAvailability</AvailableRule>
      <Criteria>
        <Criterion Id="00000000-0000-0000-0000-000000000003" Name="minimumLoanAmount" Value="1000" />
        <Criterion Id="00000000-0000-0000-0000-000000000004" Name="maximumLoanAmount" Value="25000" />
        <Criterion Id="00000000-0000-0000-0000-000000000005" Name="minimumLoanTerm" Value="12" />
        <Criterion Id="00000000-0000-0000-0000-000000000006" Name="maximumLoanTerm" Value="84" />
        <Criterion Id="00000000-0000-0000-0000-000000000001" Name="minimumAge" Value="18" />
        <Criterion Id="00000000-0000-0000-0000-000000000002" Name="maximumAge" Value="99" />
        <Criterion Id="00000000-0000-0000-0000-000000000007" Name="loanIncrementStep" Value="50" />
        <Criterion Id="00000000-0000-0000-0000-000000000008" Name="termIncrementStep" Value="6" />
      </Criteria>
      <Fees />
      <Products>
        <Product Id="DDF2AC78-8B17-11DE-A168-3E1156D89593" Name="Product 1.1 LEE">
          <Order>1</Order>
          <Rates>
            <Rate Type="APR">
              <InterestRate>25.9</InterestRate>
              <DurationType>Term</DurationType>
              <MonthsFrom>0</MonthsFrom>
              <MonthsTo>0</MonthsTo>
              <EndDate>0001-01-01T00:00:00</EndDate>
            </Rate>
          </Rates>
          <AvailableRule>CoreLoanApply_CheckProductAvailability</AvailableRule>
          <Criteria>
            <Criterion Id="00000000-0000-0000-0000-000000000003" Name="minimumLoanAmount" Value="1000" />
            <Criterion Id="00000000-0000-0000-0000-000000000004" Name="maximumLoanAmount" Value="1000" />
            <Criterion Id="00000000-0000-0000-0000-000000000001" Name="minimumAge" Value="18" />
            <Criterion Id="00000000-0000-0000-0000-000000000002" Name="maximumAge" Value="99" />
            <Criterion Id="00000000-0000-0000-0000-000000000005" Name="minimumLoanTerm" Value="12" />
            <Criterion Id="00000000-0000-0000-0000-000000000006" Name="maximumLoanTerm" Value="48" />
            <Criterion Id="00000000-0000-0000-0000-000000000007" Name="loanIncrementStep" Value="500" />
            <Criterion Id="00000000-0000-0000-0000-000000000008" Name="termIncrementStep" Value="6" />
          </Criteria>
        </Product>
        <Product Id="E4C60DB0-8B17-11DE-8611-521156D89593" Name="Product 1.2 LEE">
          <Order>1</Order>
          <Rates>
            <Rate Type="APR">
              <InterestRate>26.9</InterestRate>
              <DurationType>Term</DurationType>
              <MonthsFrom>0</MonthsFrom>
              <MonthsTo>0</MonthsTo>
              <EndDate>0001-01-01T00:00:00</EndDate>
            </Rate>
          </Rates>
          <AvailableRule>CoreLoanApply_CheckProductAvailability</AvailableRule>
          <Criteria>
            <Criterion Id="00000000-0000-0000-0000-000000000003" Name="minimumLoanAmount" Value="1000" />
            <Criterion Id="00000000-0000-0000-0000-000000000004" Name="maximumLoanAmount" Value="1000" />
            <Criterion Id="00000000-0000-0000-0000-000000000001" Name="minimumAge" Value="18" />
            <Criterion Id="00000000-0000-0000-0000-000000000002" Name="maximumAge" Value="99" />
            <Criterion Id="00000000-0000-0000-0000-000000000005" Name="minimumLoanTerm" Value="49" />
            <Criterion Id="00000000-0000-0000-0000-000000000006" Name="maximumLoanTerm" Value="84" />
            <Criterion Id="00000000-0000-0000-0000-000000000007" Name="loanIncrementStep" Value="500" />
            <Criterion Id="00000000-0000-0000-0000-000000000008" Name="termIncrementStep" Value="6" />
          </Criteria>
        </Product>
        <Product Id="E9A0C492-8B17-11DE-9170-5E1156D89593" Name="Product 1.3 LEE">
          <Order>1</Order>
          <Rates>
            <Rate Type="APR">
              <InterestRate>27.9</InterestRate>
              <DurationType>Term</DurationType>
              <MonthsFrom>0</MonthsFrom>
              <MonthsTo>0</MonthsTo>
              <EndDate>0001-01-01T00:00:00</EndDate>
            </Rate>
          </Rates>
          <AvailableRule>CoreLoanApply_CheckProductAvailability</AvailableRule>
          <Criteria>
            <Criterion Id="00000000-0000-0000-0000-000000000003" Name="minimumLoanAmount" Value="1001" />
            <Criterion Id="00000000-0000-0000-0000-000000000004" Name="maximumLoanAmount" Value="14999" />
            <Criterion Id="00000000-0000-0000-0000-000000000001" Name="minimumAge" Value="18" />
            <Criterion Id="00000000-0000-0000-0000-000000000002" Name="maximumAge" Value="99" />
            <Criterion Id="00000000-0000-0000-0000-000000000005" Name="minimumLoanTerm" Value="12" />
            <Criterion Id="00000000-0000-0000-0000-000000000006" Name="maximumLoanTerm" Value="48" />
            <Criterion Id="00000000-0000-0000-0000-000000000007" Name="loanIncrementStep" Value="500" />
            <Criterion Id="00000000-0000-0000-0000-000000000008" Name="termIncrementStep" Value="6" />
          </Criteria>
        </Product>
        <Product Id="EECB8EDE-8B17-11DE-93F3-8A1156D89593" Name="Product 1.4 LEE">
          <Order>1</Order>
          <Rates>
            <Rate Type="APR">
              <InterestRate>28.9</InterestRate>
              <DurationType>Term</DurationType>
              <MonthsFrom>0</MonthsFrom>
              <MonthsTo>0</MonthsTo>
              <EndDate>0001-01-01T00:00:00</EndDate>
            </Rate>
          </Rates>
          <AvailableRule>CoreLoanApply_CheckProductAvailability</AvailableRule>
          <Criteria>
            <Criterion Id="00000000-0000-0000-0000-000000000003" Name="minimumLoanAmount" Value="1001" />
            <Criterion Id="00000000-0000-0000-0000-000000000004" Name="maximumLoanAmount" Value="14999" />
            <Criterion Id="00000000-0000-0000-0000-000000000001" Name="minimumAge" Value="18" />
            <Criterion Id="00000000-0000-0000-0000-000000000002" Name="maximumAge" Value="99" />
            <Criterion Id="00000000-0000-0000-0000-000000000005" Name="minimumLoanTerm" Value="49" />
            <Criterion Id="00000000-0000-0000-0000-000000000006" Name="maximumLoanTerm" Value="84" />
            <Criterion Id="00000000-0000-0000-0000-000000000007" Name="loanIncrementStep" Value="500" />
            <Criterion Id="00000000-0000-0000-0000-000000000008" Name="termIncrementStep" Value="6" />
          </Criteria>
        </Product>
        <Product Id="4be2a9c8-cdd7-4dc1-8c48-dc61e5c8c976" Name="Product 1.5 LEE">
          <Order>1</Order>
          <Rates>
            <Rate Type="APR">
              <InterestRate>29.9</InterestRate>
              <DurationType>Term</DurationType>
              <MonthsFrom>0</MonthsFrom>
              <MonthsTo>0</MonthsTo>
              <EndDate>0001-01-01T00:00:00</EndDate>
            </Rate>
          </Rates>
          <AvailableRule>CoreLoanApply_CheckProductAvailability</AvailableRule>
          <Criteria>
            <Criterion Id="00000000-0000-0000-0000-000000000003" Name="minimumLoanAmount" Value="15000" />
            <Criterion Id="00000000-0000-0000-0000-000000000004" Name="maximumLoanAmount" Value="25000" />
            <Criterion Id="00000000-0000-0000-0000-000000000001" Name="minimumAge" Value="18" />
            <Criterion Id="00000000-0000-0000-0000-000000000002" Name="maximumAge" Value="99" />
            <Criterion Id="00000000-0000-0000-0000-000000000005" Name="minimumLoanTerm" Value="12" />
            <Criterion Id="00000000-0000-0000-0000-000000000006" Name="maximumLoanTerm" Value="48" />
            <Criterion Id="00000000-0000-0000-0000-000000000007" Name="loanIncrementStep" Value="500" />
            <Criterion Id="00000000-0000-0000-0000-000000000008" Name="termIncrementStep" Value="6" />
          </Criteria>
        </Product>
        <Product Id="F50358C2-8B17-11DE-8638-9E1156D89593" Name="Product 1.6 LEE">
          <Order>1</Order>
          <Rates>
            <Rate Type="APR">
              <InterestRate>30.9</InterestRate>
              <DurationType>Term</DurationType>
              <MonthsFrom>0</MonthsFrom>
              <MonthsTo>0</MonthsTo>
              <EndDate>0001-01-01T00:00:00</EndDate>
            </Rate>
          </Rates>
          <AvailableRule>CoreLoanApply_CheckProductAvailability</AvailableRule>
          <Criteria>
            <Criterion Id="00000000-0000-0000-0000-000000000003" Name="minimumLoanAmount" Value="15000" />
            <Criterion Id="00000000-0000-0000-0000-000000000004" Name="maximumLoanAmount" Value="25000" />
            <Criterion Id="00000000-0000-0000-0000-000000000001" Name="minimumAge" Value="18" />
            <Criterion Id="00000000-0000-0000-0000-000000000002" Name="maximumAge" Value="99" />
            <Criterion Id="00000000-0000-0000-0000-000000000005" Name="minimumLoanTerm" Value="49" />
            <Criterion Id="00000000-0000-0000-0000-000000000006" Name="maximumLoanTerm" Value="84" />
            <Criterion Id="00000000-0000-0000-0000-000000000007" Name="loanIncrementStep" Value="500" />
            <Criterion Id="00000000-0000-0000-0000-000000000008" Name="termIncrementStep" Value="6" />
          </Criteria>
        </Product>
      </Products>
      <ProductOptions>
        <ProductOption Code="None">
          <Parameters>
            <Parameter Name="Premium">0</Parameter>
          </Parameters>
        </ProductOption>
        <ProductOption Code="DeferredPeriod">
          <Parameters>
            <Parameter Name="Period">2</Parameter>
          </Parameters>
        </ProductOption>
        <ProductOption Code="DefaultResponseCode">
          <Parameters>
            <Parameter Name="Code">SBSS01LEE</Parameter>
          </Parameters>
        </ProductOption>
      </ProductOptions>
    </ProductClass>
</ProductClasses>
</ProductRange>

What I need to do is the following:
I need each Product Ids
<Products>
        <Product Id="DDF2AC78-8B17-11DE-A168-3E1156D89593" Name="Product 1.1 LEE">

To be associated with its Criteria
<Criteria>
            <Criterion Id="00000000-0000-0000-0000-000000000003" Name="minimumLoanAmount" Value="1000" />
            <Criterion Id="00000000-0000-0000-0000-000000000004" Name="maximumLoanAmount" Value="1000" />
            <Criterion Id="00000000-0000-0000-0000-000000000001" Name="minimumAge" Value="18" />
            <Criterion Id="00000000-0000-0000-0000-000000000002" Name="maximumAge" Value="99" />
            <Criterion Id="00000000-0000-0000-0000-000000000005" Name="minimumLoanTerm" Value="12" />
            <Criterion Id="00000000-0000-0000-0000-000000000006" Name="maximumLoanTerm" Value="48" />
            <Criterion Id="00000000-0000-0000-0000-000000000007" Name="loanIncrementStep" Value="500" />
            <Criterion Id="00000000-0000-0000-0000-000000000008" Name="termIncrementStep" Value="6" />
          </Criteria>
'
So I have a table like the following:

create table #Criteria (Product_Id UniqueIdentifier, Criteria_Id UniqueIdentifier, Criteria_Name nvarchar(255), Criteria_Value Int)

I have managed to get the following
;WITH XMLNAMESPACES(
DEFAULT 'http://schemas.i.com/2008/01/product '
)
Insert into #Criteria(Product_Id, Criteria_Id, Criteria_Name, Criteria_Value)
SELECT

    p.value('@Id[1]','VARCHAR(255)
') AS Product_Id,
    x.value('@Id[1]','VARCHAR(255)') AS Criteria_Id,
    x.value('@Name[1]','VARCHAR(255)') AS Criteria_Name,
    x.value('@Value[1]','VARCHAR(255)') AS Criteria_Value
FROM @p3.nodes('ProductRange/ProductClasses/ProductClass/Products/Product') a(p)
OUTER APPLY
@p3.nodes('ProductRange/ProductClasses/ProductClass/Products/Product/Criteria/Criterion') x(x)

However this returns 288 rows (not the 48 rows I need) due to the outer apply of course
I have tried
FROM @p3.nodes('ProductRange/ProductClasses/ProductClass/Products/Product') a(p)
OUTER APPLY
p.nodes('ProductRange/ProductClasses/ProductClass/Products/Product/Criteria/Criterion') x(x)

But this just returns the Product Ids with no associated criteria.

SO anyone know how to return a product id and all its associated criteria?

Need help urgently as I am pretty stumped.

Thanks

Lee

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-18 : 15:39:15

You mean like this?

;WITH		XMLNAMESPACES(DEFAULT 'http://schemas.i.com/2008/01/product ')
SELECT		p.n.value('@Id[1]', 'UNIQUEIDENTIFIER') AS Product_Id,
		n.c.value('@Id[1]', 'UNIQUEIDENTIFIER') AS Criteria_Id,
		n.c.value('@Name[1]', 'VARCHAR(20)') AS Criteria_Name,
		n.c.value('@Value[1]', 'INT') AS Criteria_Value
FROM		@p3.nodes('/ProductRange/ProductClasses/ProductClass/Products/Product') AS p(n)
CROSS APPLY	p.n.nodes('Criteria/Criterion') AS n(c)

N 56°04'39.26"
E 12°55'05.63"

Leecau
Starting Member

5 Posts

Posted - 2009-08-28 : 06:50:36

Thanks! Works perfect -
Was just missing the ('Criteria/Criterion') part - so easy when you see it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources