Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL 2005  XQuery  Node with multiple attribute help

Posted on 2010-01-08
1
Medium Priority
?
560 Views
Last Modified: 2013-11-11
Im new to XML and have to generate the follow mapping node from a SQL 2005 stored procedure for products to be imported into another application:

<Product>
  <Title>Hell Girl - Lithograph</ Title>
  <SKU>112233445566</SKU>
  <ManufacturerPartNumber>1234567</ManufacturerPartNumber>
  <ProductType>Merchandise</ProductType>
  <AvaiableStartDate>2008-10-07</AvaiableStartDate>
  <AvaiableStopDate>1900-01-01</AvaiableStopDate>

  <Mappings>
    <Entity EntityType="Distributor" Name="ABC Company"/>
    <Entity EntityType="Manufacturer" Name="EFG Manufacturer"/>
    <Entity EntityType="Category" Name="Lithograph"/>
    <Entity EntityType="Property" Name="Hell Girl"/>
  </Mappings>

</Product>

But my code generates this:

<Product>
  < Title >Hell Girl - Lithograph</ Title>
  <SKU>112233445566</SKU>
  <ManufacturerPartNumber>1234567</ManufacturerPartNumber>
  <ProductType>Merchandise</ProductType>
  <AvaiableStartDate>2008-10-07</AvaiableStartDate>
  <AvaiableStopDate>1900-01-01</AvaiableStopDate>

  <Mappings>
    <Entity EntityType="Distributor">ABC Company</Entity>
    <Entity EntityType="Manufacturer">EFG Manufacturer</Entity>
    <Entity EntityType="Category">Lithograph</Entity>
    <Entity EntityType="Property">Hell Girl</Entity>
  </Mappings>

</Product>

Attached is code snippet from my stored procedure. Is there a way to generate multiple attributes for a node or is this not supported in SQL 2005 XML?

Thank you,
Larry B.
SELECT
	fun.Title AS "Title",
	sap.UPCNumber AS "SKU",	
	sap.MaterialNumber AS "ManufacturerPartNumber",
	fun.ProductType AS "ProductType",
	fun.ReleaseDate AS "AvaiableStartDate",
	sap.StopSalesDate AS "AvaiableStopDate",
	(
	SELECT
	'ABC Company' AS [Distributor],
	fun2.Manufacturer,
	fun2.Category,
	fun2.Property
	FROM dbo.AspDNSFStaging_FUNCat_Material fun2
	INNER JOIN dbo.AspDNSFIStaging_SAP_FUN_Material sap2 ON CAST(fun2.UPC AS BIGINT) = CAST(sap2.UPCNumber AS BIGINT)
	WHERE sap2.UPCNumber = sap.UPCNumber		
	FOR XML RAW ('Entity'), TYPE
	)
	.query
	('
	<Mappings>  
    <Entity EntityType="Distributor">{data(/Entity/@Distributor)}</Entity>
    <Entity EntityType="Manufacturer">{data(/Entity/@Manufacturer)}</Entity> 
    <Entity EntityType="Category">{data(/Entity/@Category)}</Entity> 
    <Entity EntityType="Property">{data(/Entity/@Property)}</Entity>
    </Mappings>
	')
	FROM dbo.AspDNSFStaging_FUNCat_Material fun
	INNER JOIN dbo.AspDNSFIStaging_SAP_FUN_Material sap ON CAST(fun.UPC AS BIGINT) = CAST(sap.UPCNumber AS BIGINT)
ORDER BY sap.UPCNumber
FOR XML PATH('Product'), TYPE

Open in new window

0
Comment
Question by:larrybye
1 Comment
 
LVL 26

Accepted Solution

by:
tigin44 earned 2000 total points
ID: 26279678
this should give you the results
SELECT
        fun.Title AS "Title",
        sap.UPCNumber AS "SKU", 
        sap.MaterialNumber AS "ManufacturerPartNumber",
        fun.ProductType AS "ProductType",
        fun.ReleaseDate AS "AvaiableStartDate",
        sap.StopSalesDate AS "AvaiableStopDate",
        (
        SELECT
        'ABC Company' AS [Distributor],
        fun2.Manufacturer,
        fun2.Category,
        fun2.Property
        FROM dbo.AspDNSFStaging_FUNCat_Material fun2
        INNER JOIN dbo.AspDNSFIStaging_SAP_FUN_Material sap2 ON CAST(fun2.UPC AS BIGINT) = CAST(sap2.UPCNumber AS BIGINT)
        WHERE sap2.UPCNumber = sap.UPCNumber            
        FOR XML RAW ('Entity'), TYPE
        )
        .query
        ('
        <Mappings>  
    <Entity EntityType="Distributor" Name="{data(/Entity/@Distributor)}"/>
    <Entity EntityType="Manufacturer" Name="{data(/Entity/@Manufacturer)}"/> 
    <Entity EntityType="Category" Name="{data(/Entity/@Category)}"/> 
    <Entity EntityType="Property" Name="{data(/Entity/@Property)}"/>
    </Mappings>
        ')
        FROM dbo.AspDNSFStaging_FUNCat_Material fun
        INNER JOIN dbo.AspDNSFIStaging_SAP_FUN_Material sap ON CAST(fun.UPC AS BIGINT) = CAST(sap.UPCNumber AS BIGINT)
ORDER BY sap.UPCNumber
FOR XML PATH('Product'), TYPE

Open in new window

0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Browsing the questions asked to the Experts of this forum, you will be amazed to see how many times people are headaching about monster regular expressions (regex) to select that specific part of some HTML or XML file they want to extract. The examp…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question