英文:
Generate XML files with various namespaces using SQL Server
问题
以下是您要翻译的内容:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<kmsg xmlns='http://xml.aaa.net/K809' xmlns:env='http://xml.aaa.net/K809/k8msgEnvelope' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xsi:schemaLocation='http://xml.aaa.net/K809 k8Order.xsd'>
<header>
<env:envelope>
<env:source branch='2172' endpoint='' machine='0' password=''/>
<env:destination branch='2172' endpoint='050107' machine='0'/>
<env:payload>ORDERRESPONSE</env:payload>
<env:cfcompany>01</env:cfcompany>
<env:service>ILDLIVE</env:service>
</env:envelope>
</header>
<body>
<PurchaseOrder xmlns='urn:schemas-basda-org:2000:purchaseOrder:xdr:3.01'>
<OrderReferences>
<CrossReference>ABC 1234567</CrossReference>
</OrderReferences>
<Extensions xmlns='http://xml.aaa.net/k8msg/k8OrderExtensions'>
<Direct>FALSE</Direct>
</Extensions>
<Supplier>
<SupplierReferences>
<BuyersCodeForSupplier>050107</BuyersCodeForSupplier>
</SupplierReferences>
</Supplier>
<Delivery>
<PreferredDate>2022-12-24</PreferredDate>
<SpecialInstructions>Please contact store buyer to confirm quantities and prices, all orders and prices will be final after acceptance of order</SpecialInstructions>
</Delivery>
<OrderLine Action='Add' TypeCode='New' TypeDescription='New Item'>
<Product>
<SuppliersProductCode>1000486</SuppliersProductCode>
<BuyersProductCode>1000486</BuyersProductCode>
</Product>
<Quantity UOMCode='EA' UOMDescription='Each'>
<Amount>10</Amount>
</Quantity>
<Delivery>
<PreferredDate>2022-12-24</PreferredDate>
</Delivery>
</OrderLine>
</PurchaseOrder>
</body>
</kmsg>
请注意,我已经去掉了代码部分,只返回了您要的XML内容的翻译。
英文:
I need to generate XML from SQL Server that the end result looks like this:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<kmsg xmlns='http://xml.aaa.net/K809' xmlns:env='http://xml.aaa.net/K809/k8msgEnvelope' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xsi:schemaLocation='http://xml.aaa.net/K809 k8Order.xsd'>
<header>
<env:envelope>
<env:source branch='2172' endpoint='' machine='0' password=''/>
<env:destination branch='2172' endpoint='050107' machine='0'/>
<env:payload>ORDERRESPONSE</env:payload>
<env:cfcompany>01</env:cfcompany>
<env:service>ILDLIVE</env:service>
</env:envelope>
</header>
<body>
<PurchaseOrder xmlns='urn:schemas-basda-org:2000:purchaseOrder:xdr:3.01'>
<OrderReferences>
<CrossReference>ABC 1234567</CrossReference>
</OrderReferences>
<Extensions xmlns='http://xml.aaa.net/k8msg/k8OrderExtensions'>
<Direct>FALSE</Direct>
</Extensions>
<Supplier>
<SupplierReferences>
<BuyersCodeForSupplier>050107</BuyersCodeForSupplier>
</SupplierReferences>
</Supplier>
<Delivery>
<PreferredDate>2022-12-24</PreferredDate>
<SpecialInstructions>Please contact store buyer to confirm quantities and prices, all orders and prices will be final after acceptance of order</SpecialInstructions>
</Delivery>
<OrderLine Action='Add' TypeCode='New' TypeDescription='New Item'>
<Product>
<SuppliersProductCode>1000486</SuppliersProductCode>
<BuyersProductCode>1000486</BuyersProductCode>
</Product>
<Quantity UOMCode='EA' UOMDescription='Each'>
<Amount>10</Amount>
</Quantity>
<Delivery>
<PreferredDate>2022-12-24</PreferredDate>
</Delivery>
</OrderLine>
</PurchaseOrder>
</body>
</kmsg>
The basic layout has been done but I'm facing trouble with 2 items:
- The namespaces at the top. I'm generating the namespaces but it generates out of sync in order of creation and I'm also getting " characters. I suspect a characterset mismatch or something similar. How do I get the namespaces to view in the exact order of the document?
- How do I add namespaces in the middle of the document? The PurchaseOrder and Extentions tags both have namespaces within them. Can these also be declared at the top of the query, or do I need to add them as static values in SQL?
Here is the code I have currently:
WITH XMLNAMESPACES (
'xsi="http://xml.aaa.net/K809 k8Order.xsd"' AS schemaLocation,
'xsi="http://www.w3.org/2001/XMLSchema-instance"' AS xsi,
'env="http://xml.aaa.net/K809/k8msgEnvelope"' AS env,
'xmlns="http://xml.aaa.net/K809"' AS xmlns1)
SELECT TOP 1
'' AS 'header/env:envelope/env:source/@password',
'' AS 'header/env:envelope/env:source/@machine',
A.SUPPLIER AS 'header/env:envelope/env:source/@Endpoint',
B.SRCLOC AS 'header/env:envelope/env:source/@Branch',
'ORDERRESPONSE' AS 'header/env:envelope/env:payload',
--B.SRCLOC AS DestinationBranch,
C.FREEATTR3 AS 'header/env:envelope/env:cfCompany',
'ILDLive' AS 'header/env:envelope/env:service',
'urn:schemas-basda-org:2000:purchaseOrder:xdr:3>' AS 'Body/@PurchaseOrder',
A.SUPPLIER AS 'Body/PurchaseOrder/Supplier/SupplierReferences/BuyersCodeForSupplier',
DATEDIFF(DAY,'1989/12/31',A.EXPDATEPRD) AS 'Body/PurchaseOrder/Delivery/PreferredDate', --CONVERT: 2022-12-24
'Test text for Special instructions' AS 'Body/PurchaseOrder/Delivery/SpecialInstructions',
'New item' AS 'Body/PurchaseOrder/OrderLine/@TypeDescription',
'New' AS 'Body/PurchaseOrder/OrderLine/@TypeCode',
'Add' AS 'Body/PurchaseOrder/OrderLine/@Action',
A.ITEM AS 'Body/PurchaseOrder/OrderLine/Product/BuyersProductCode',
D.ExternalItemMasterID AS 'Body/PurchaseOrder/OrderLine/Product/SuppliersProductCode',
F.UnitOfMeasureDesc AS 'Body/PurchaseOrder/OrderLine/Quantity/@UOMDescription',
E.VolumetricValue AS 'Body/PurchaseOrder/OrderLine/Quantity/@UOMCode',
CAST(SUM(A.QEDIT) AS INT) AS 'Body/PurchaseOrder/OrderLine/Quantity/Amount'
--INTO #XMLTemp
FROM TableMain AS A
JOIN Table1 AS B ON A.DESTWHS = B.SRCWHS
JOIN Table2 AS C ON B.SRCLOC = C.LOCATION
JOIN Table3 AS D ON A.ITEM = D.ItemCode
JOIN Table4 AS E ON A.ITEM = E.ItemCode
JOIN Table5 AS F ON F.ItemCode=A.ITEM
WHERE E.Measurement = 'Unit of Buy'
--AND DATEDIFF(DAY,'1989/12/31',A.PRUNDATPRD) = GETDATE()
--ONLY THE LATEST PROPOSALS
GROUP BY B.SRCLOC,C.FREEATTR3,A.SUPPLIER,A.EXPDATEPRD,D.ExternalItemMasterID,A.ITEM,E.VolumetricValue,F.UnitOfMeasureDesc
FOR XML PATH('kmsg')
and here is the output I have:
<kmsg xmlns:xmlns1="xmlns=&quot;http://xml.aaa.net/K809&quot;" xmlns:env="env=&quot;http://xml.aaa.net/K809/k8msgEnvelope&quot;" xmlns:xsi="xsi=&quot;http://www.w3.org/2001/XMLSchema-instance&quot;" xmlns:schemaLocation="xsi=&quot;http://xml.aaa.net/K809 k8Order.xsd&quot;">
<header>
<env:envelope>
<env:source password="" machine="" Endpoint="050354" Branch="1012" />
<env:payload>ORDERRESPONSE</env:payload>
<env:cfCompany>01</env:cfCompany>
<env:service>ILDLive</env:service>
</env:envelope>
</header>
<Body PurchaseOrder="urn:schemas-basda-org:2000:purchaseOrder:xdr:3&gt;">
<PurchaseOrder>
<Supplier>
<SupplierReferences>
<BuyersCodeForSupplier>050354</BuyersCodeForSupplier>
</SupplierReferences>
</Supplier>
<Delivery>
<PreferredDate>-20744</PreferredDate>
<SpecialInstructions>Test text for Special instructions</SpecialInstructions>
</Delivery>
<OrderLine TypeDescription="New item" TypeCode="New" Action="Add">
<Product>
<BuyersProductCode>1034623</BuyersProductCode>
<SuppliersProductCode>KHN4.200CX</SuppliersProductCode>
</Product>
<Quantity UOMDescription="Each" UOMCode="EA">
<Amount>2904</Amount>
</Quantity>
</OrderLine>
</PurchaseOrder>
</Body>
</kmsg>
Any help would be appreciated. I'm not used to working with XML in SQL.
I tried using static namespaces in the SQL part but I don't think that is the way to do it
答案1
得分: 0
以下是代码部分的翻译:
<kmsg xmlns="http://xml.aaa.net/K809"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:env="http://xml.aaa.net/K809/k8msgEnvelope"
xmlns:po="urn:schemas-basda-org:2000:purchaseOrder:xdr:3.01" xsi:schemaLocation="http://xml.aaa.net/K809 k8Order.xsd">
<header>
<env:envelope>
<env:source password="" machine="" endpoint="050354" branch="2172"/>
<env:payload>ORDERRESPONSE</env:payload>
<env:cfcompany>01</env:cfcompany>
<env:service>ILDLive</env:service>
</env:envelope>
</header>
<body>
<PurchaseOrder xmlns="urn:schemas-basda-org:2000:purchaseOrder:xdr:3.01">
<Extensions xmlns="http://xml.aaa.net/k8msg/k8OrderExtensions">
<Direct>FALSE</Direct>
</Extensions>
<Supplier>
<SupplierReferences>
<BuyersCodeForSupplier>050354</BuyersCodeForSupplier>
</SupplierReferences>
</Supplier>
<Delivery>
<PreferredDate>2022-12-24</PreferredDate>
<SpecialInstructions>Test text for Special instructions</SpecialInstructions>
</Delivery>
<OrderLine TypeDescription="New item" TypeCode="New" Action="Add">
<Product>
<BuyersProductCode>1034623</BuyersProductCode>
<SuppliersProductCode>KHN4.200CX</SuppliersProductCode>
</Product>
<Quantity UOMDescription="Each" UOMCode="EA">
<Amount>10</Amount>
</Quantity>
</OrderLine>
</PurchaseOrder>
</body>
</kmsg>
英文:
Given some of the errors there are several things you need to keep in mind with XML documents:
- namespace URIs are case sensitive.
- element names are case sensitive, so
<Body>
is a different element than<body>
. - attribute names case sensitive, so
<source Endpoint="..."/>
is a different attribute than<source endpoint="..."/>
.
WITH XMLNAMESPACES
has a particular syntax where 'urn' as prefix
defines a namespace URN with a prefix and default 'urn'
defines a namespace URN to be used without a prefix, aka. the default namespace.
The following SQL probably isn't correct for your environment but produces XML similar to the output you are looking for - if you update your question to include sample DDL and data this could be tweaked somewhat:
--
-- Setup data...
--
select 'WHS' as DESTWHS, cast('2022-12-24' as date) as EXPDATEPRD, '1034623' as ITEM, 10 as QEDIT, '050354' as SUPPLIER
into dbo.TableMain; --A
select '2172' as SRCLOC, 'WHS' as SRCWHS
into dbo.Table1; --B
select '01' as FREEATTR3, '2172' as [LOCATION]
into dbo.Table2; --C
select 'KHN4.200CX' as ExternalItemMasterID, '1034623' as ItemCode
into dbo.Table3; --D
select '1034623' as ItemCode, 'Unit of Buy' as Measurement, 'EA' as VolumetricValue
into dbo.Table4; --E
select '1034623' as ItemCode, 'Each' as UnitOfMeasureDesc
into dbo.Table5; --F
--
-- XML query...
--
WITH XMLNAMESPACES (
'urn:schemas-basda-org:2000:purchaseOrder:xdr:3.01' as po,
'http://xml.aaa.net/K809/k8msgEnvelope' AS env,
'http://www.w3.org/2001/XMLSchema-instance' as xsi,
default 'http://xml.aaa.net/K809'
)
SELECT TOP 1
'http://xml.aaa.net/K809 k8Order.xsd' as [@xsi:schemaLocation],
B.SRCLOC AS [header/env:envelope/env:source/@branch],
A.SUPPLIER AS [header/env:envelope/env:source/@endpoint],
'' AS [header/env:envelope/env:source/@machine],
'' AS [header/env:envelope/env:source/@password],
'ORDERRESPONSE' AS [header/env:envelope/env:payload],
C.FREEATTR3 AS [header/env:envelope/env:cfcompany],
'ILDLive' AS [header/env:envelope/env:service],
(
select
cast('<Extensions xmlns="http://xml.aaa.net/k8msg/k8OrderExtensions"><Direct>FALSE</Direct></Extensions>' as xml).query('/*'),
A.SUPPLIER AS [po:Supplier/po:SupplierReferences/po:BuyersCodeForSupplier],
A.EXPDATEPRD AS [po:Delivery/po:PreferredDate],
'Test text for Special instructions' AS [po:Delivery/po:SpecialInstructions],
'New item' AS [po:OrderLine/@TypeDescription],
'New' AS [po:OrderLine/@TypeCode],
'Add' AS [po:OrderLine/@Action],
A.ITEM AS [po:OrderLine/po:Product/po:BuyersProductCode],
D.ExternalItemMasterID AS [po:OrderLine/po:Product/po:SuppliersProductCode],
F.UnitOfMeasureDesc AS [po:OrderLine/po:Quantity/@UOMDescription],
E.VolumetricValue AS [po:OrderLine/po:Quantity/@UOMCode],
CAST(SUM(A.QEDIT) AS INT) AS [po:OrderLine/po:Quantity/po:Amount]
for xml path('po:PurchaseOrder'), type
).query('
declare default element namespace "urn:schemas-basda-org:2000:purchaseOrder:xdr:3.01";
<PurchaseOrder> { /po:PurchaseOrder/* } </PurchaseOrder>') as [body]
FROM TableMain AS A
JOIN Table1 AS B ON A.DESTWHS = B.SRCWHS
JOIN Table2 AS C ON B.SRCLOC = C.LOCATION
JOIN Table3 AS D ON A.ITEM = D.ItemCode
JOIN Table4 AS E ON A.ITEM = E.ItemCode
JOIN Table5 AS F ON F.ItemCode=A.ITEM
WHERE E.Measurement = 'Unit of Buy'
GROUP BY B.SRCLOC,C.FREEATTR3,A.SUPPLIER,A.EXPDATEPRD,D.ExternalItemMasterID,A.ITEM,E.VolumetricValue,F.UnitOfMeasureDesc
FOR XML PATH('kmsg')
Which yields the XML output:
<kmsg xmlns="http://xml.aaa.net/K809"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:env="http://xml.aaa.net/K809/k8msgEnvelope"
xmlns:po="urn:schemas-basda-org:2000:purchaseOrder:xdr:3.01" xsi:schemaLocation="http://xml.aaa.net/K809 k8Order.xsd">
<header>
<env:envelope>
<env:source password="" machine="" endpoint="050354" branch="2172"/>
<env:payload>ORDERRESPONSE</env:payload>
<env:cfcompany>01</env:cfcompany>
<env:service>ILDLive</env:service>
</env:envelope>
</header>
<body>
<PurchaseOrder xmlns="urn:schemas-basda-org:2000:purchaseOrder:xdr:3.01">
<Extensions xmlns="http://xml.aaa.net/k8msg/k8OrderExtensions">
<Direct>FALSE</Direct>
</Extensions>
<Supplier>
<SupplierReferences>
<BuyersCodeForSupplier>050354</BuyersCodeForSupplier>
</SupplierReferences>
</Supplier>
<Delivery>
<PreferredDate>2022-12-24</PreferredDate>
<SpecialInstructions>Test text for Special instructions</SpecialInstructions>
</Delivery>
<OrderLine TypeDescription="New item" TypeCode="New" Action="Add">
<Product>
<BuyersProductCode>1034623</BuyersProductCode>
<SuppliersProductCode>KHN4.200CX</SuppliersProductCode>
</Product>
<Quantity UOMDescription="Each" UOMCode="EA">
<Amount>10</Amount>
</Quantity>
</OrderLine>
</PurchaseOrder>
</body>
</kmsg>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论