December 20, 2018 at 8:46 pm
Hi All
I have been working on this for a couple days now and can't figure it out. I am trying to query a string in an XML column from a table. I can get only the 1st value to return. I need all the values in the XML. Basically, this table holds activation keys for multiple licenses. There are multiple licenses for one activation code and all those licenses are stored in an XML string.
CREATE TABLE TBLA (
Customer varchar(100) NULL,
ActivationKey varchar(100) NULL,
OrderXML XML NULL
)
The XML string looks like this:
<ns1:Order xmlns:ns1="http://company.com/licensing/neworder/v2">
<ns1:AccountInfo Name="NewCustomerName" CRMAccountId="A021111" />
<ns1:OrderInfo CRMOrderId="S12345.A020739.12/17/2018 7:23:13 AM" Date="12/17/2018 12:00:00 AM" FulfillmentContactEmail="cathryncrane@gmail.com" FCOrganizationalLevel="" >
<ns1:Licensing>
<ns1:Licenses Type="Update">
<ns1:License LicenseId="L1111111" StartDate="2017-10-12" >
<ns1:Capabilities>
<ns1:Capability Name="Pull" />
<ns1:Capability Name="Query" />
</ns1:Capabilities>
</ns1:License>
<ns1:License LicenseId="L1111123" StartDate="2017-10-12" >
<ns1:Capabilities>
<ns1:Capability Name="Push" />
<ns1:Capability Name="Query" />
</ns1:Capabilities>
</ns1:License>
<ns1:License LicenseId="L111145" StartDate="2017-10-12" >
<ns1:Capabilities>
<ns1:Capability Name="Pull" />
<ns1:Capability Name="Query" />
</ns1:Capabilities>
</ns1:License>
</ns1:Licenses>
</ns1:Licensing>
</ns1:OrderInfo>
</ns1:Order>
This is the SQL
;WITH XMLNAMESPACES
(default 'http://winshuttle.com/licensing/neworder/v2')
SELECT activationcode,
CustomerName,
[OrderXMLText].value('(OrderFromCRM/OrderInfo/Licensing/Licenses/@Type)[1]','varchar(100)') as [Type]
[OrderXMLText].value('(OrderFromCRM/OrderInfo/Licensing/Licenses/License/@LicenseId)[1]','varchar(100)') as [CRMLicenseId]
FROM #TMPA
Although the XML has 3 licenses, it only returns 1 row -- the 1st license value in the XML String
How do I need to write this so I can flatten the XML into a table with a row with each license and the activation code associated with it. The activation code would be repeated for it's associated license.
Thank you!
December 21, 2018 at 12:22 am
Here is an example that should get you passed this hurdle.
😎
USE TEEST;
GO
SET NOCOUNT ON;
DECLARE @TXML XML = '<ns1:Order xmlns:ns1="http://company.com/licensing/neworder/v2">
<ns1:AccountInfo Name="NewCustomerName" CRMAccountId="A021111" />
<ns1:OrderInfo CRMOrderId="S12345.A020739.12/17/2018 7:23:13 AM" Date="12/17/2018 12:00:00 AM" FulfillmentContactEmail="cathryncrane@gmail.com" FCOrganizationalLevel="" >
<ns1:Licensing>
<ns1:Licenses Type="Update">
<ns1:License LicenseId="L1111111" StartDate="2017-10-12" >
<ns1:Capabilities>
<ns1:Capability Name="Pull" />
<ns1:Capability Name="Query" />
</ns1:Capabilities>
</ns1:License>
<ns1:License LicenseId="L1111123" StartDate="2017-10-12" >
<ns1:Capabilities>
<ns1:Capability Name="Push" />
<ns1:Capability Name="Query" />
</ns1:Capabilities>
</ns1:License>
<ns1:License LicenseId="L111145" StartDate="2017-10-12" >
<ns1:Capabilities>
<ns1:Capability Name="Pull" />
<ns1:Capability Name="Query" />
</ns1:Capabilities>
</ns1:License>
</ns1:Licenses>
</ns1:Licensing>
</ns1:OrderInfo>
</ns1:Order>';
;WITH XMLNAMESPACES (DEFAULT 'http://company.com/licensing/neworder/v2')
SELECT
ORD.DATA.value('(AccountInfo/@Name)[1]' ,'VARCHAR(50)' ) AS AccountInfo_Name
,ORD.DATA.value('(AccountInfo/@CRMAccountId)[1]' ,'VARCHAR(10)' ) AS AccountInfo_CRMAccountId
,ORD.DATA.value('(OrderInfo/@CRMOrderId)[1]' ,'VARCHAR(100)') AS OrderInfo_CRMOrderId
,ORD.DATA.value('(OrderInfo/@Date)[1]' ,'DATE' ) AS OrderInfo_Date
,ORD.DATA.value('(OrderInfo/@FulfillmentContactEmail)[1]','VARCHAR(100)') AS OrderInfo_FulfillmentContactEmail
,ORD.DATA.value('(OrderInfo/@FCOrganizationalLevel)[1]' ,'VARCHAR(100)') AS OrderInfo_FCOrganizationalLevel
,LIC.DATA.value('@LicenseId' ,'VARCHAR(10)' ) AS LicenseId
,LIC.DATA.value('@StartDate' ,'DATE' ) AS StartDate
,LIC.DATA.value('(Capabilities/Capability/@Name)[1]' ,'VARCHAR(10)' ) AS Capability_1
,LIC.DATA.value('(Capabilities/Capability/@Name)[2]' ,'VARCHAR(10)' ) AS Capability_2
FROM @TXML.nodes('Order') ORD(DATA)
CROSS APPLY ORD.DATA.nodes('OrderInfo/Licensing/Licenses/License') LIC(DATA);
Output
AccountInfo_Name AccountInfo_CRMAccountId OrderInfo_CRMOrderId OrderInfo_Date OrderInfo_FulfillmentContactEmail OrderInfo_FCOrganizationalLevel LicenseId StartDate Capability_1 Capability_2
----------------- ------------------------ ------------------------------------- -------------- ---------------------------------- -------------------------------- ---------- ---------- ------------ ------------
NewCustomerName A021111 S12345.A020739.12/17/2018 7:23:13 AM 2018-12-17 cathryncrane@gmail.com L1111111 2017-10-12 Pull Query
NewCustomerName A021111 S12345.A020739.12/17/2018 7:23:13 AM 2018-12-17 cathryncrane@gmail.com L1111123 2017-10-12 Push Query
NewCustomerName A021111 S12345.A020739.12/17/2018 7:23:13 AM 2018-12-17 cathryncrane@gmail.com L111145 2017-10-12 Pull Query
December 27, 2018 at 10:26 am
Thank you SO MUCH! That worked perfectly!
December 27, 2018 at 11:17 am
So, now instead of using an XML Variable, can you use this with a table? The data is in a table with an activation code associated with multiple licenses stored in the XML string. I don't want to use a cursor. I'm hoping I can just use the table. I need to pull the first 2 columns with all the associatedCREATE TABLE TBLA (
Customer varchar(100) NULL,
ActivationKey varchar(100) NULL,
OrderXML XML NULL
)
I've tried replacing the @xml variable with the tablename with the crossapply and a join statement but I can't get it to work properly.
FROM #TMPA ORD
JOIN #TMPA LIC ON ORD.ActivationCode = LIC.ActivationCode
It only gives me the 1st License again.
It should be:
CompanyName, ActivationCode, LicenseId1
CompanyName, ActivationCode, LicenseId2
CompanyName, ActivationCode, LicenseId3
Thank you in advance
December 27, 2018 at 1:24 pm
OlyKLin - Thursday, December 27, 2018 11:17 AMSo, now instead of using an XML Variable, can you use this with a table? The data is in a table with an activation code associated with multiple licenses stored in the XML string. I don't want to use a cursor. I'm hoping I can just use the table. I need to pull the first 2 columns with all the associatedCREATE TABLE TBLA (
Customer varchar(100) NULL,
ActivationKey varchar(100) NULL,
OrderXML XML NULL
)
I've tried replacing the @xml variable with the tablename with the crossapply and a join statement but I can't get it to work properly.
FROM #TMPA ORD
JOIN #TMPA LIC ON ORD.ActivationCode = LIC.ActivationCodeIt only gives me the 1st License again.
It should be:
CompanyName, ActivationCode, LicenseId1
CompanyName, ActivationCode, LicenseId2
CompanyName, ActivationCode, LicenseId3Thank you in advance
Are you using a permanent table or a temporary table? Your post has both.
December 27, 2018 at 4:10 pm
I'm sorry - I'm pulling a subset of records into a Temporary Table.
So, the XML parsing is from the temporary table
December 27, 2018 at 11:58 pm
Here is a table example
😎
USE TEEST;
GO
SET NOCOUNT ON;
DECLARE @TXML XML = '<ns1:Order xmlns:ns1="http://company.com/licensing/neworder/v2">
<ns1:AccountInfo Name="NewCustomerName" CRMAccountId="A021111" />
<ns1:OrderInfo CRMOrderId="S12345.A020739.12/17/2018 7:23:13 AM" Date="12/17/2018 12:00:00 AM" FulfillmentContactEmail="cathryncrane@gmail.com" FCOrganizationalLevel="" >
<ns1:Licensing>
<ns1:Licenses Type="Update">
<ns1:License LicenseId="L1111111" StartDate="2017-10-12" >
<ns1:Capabilities>
<ns1:Capability Name="Pull" />
<ns1:Capability Name="Query" />
</ns1:Capabilities>
</ns1:License>
<ns1:License LicenseId="L1111123" StartDate="2017-10-12" >
<ns1:Capabilities>
<ns1:Capability Name="Push" />
<ns1:Capability Name="Query" />
</ns1:Capabilities>
</ns1:License>
<ns1:License LicenseId="L111145" StartDate="2017-10-12" >
<ns1:Capabilities>
<ns1:Capability Name="Pull" />
<ns1:Capability Name="Query" />
</ns1:Capabilities>
</ns1:License>
</ns1:Licenses>
</ns1:Licensing>
</ns1:OrderInfo>
</ns1:Order>';
DECLARE @TBLA TABLE
(
Customer VARCHAR(100) NULL
,ActivationKey VARCHAR(100) NULL
,OrderXML XML NULL
);
INSERT INTO @TBLA (Customer,ActivationKey,OrderXML)
VALUES ('CUST_01','ABC123',@TXML);
;WITH XMLNAMESPACES (DEFAULT 'http://company.com/licensing/neworder/v2')
SELECT
TA.Customer
,TA.ActivationKey
,ORD.DATA.value('(AccountInfo/@Name)[1]' ,'VARCHAR(50)' ) AS AccountInfo_Name
,ORD.DATA.value('(AccountInfo/@CRMAccountId)[1]' ,'VARCHAR(10)' ) AS AccountInfo_CRMAccountId
,ORD.DATA.value('(OrderInfo/@CRMOrderId)[1]' ,'VARCHAR(100)') AS OrderInfo_CRMOrderId
,ORD.DATA.value('(OrderInfo/@Date)[1]' ,'DATE' ) AS OrderInfo_Date
,ORD.DATA.value('(OrderInfo/@FulfillmentContactEmail)[1]','VARCHAR(100)') AS OrderInfo_FulfillmentContactEmail
,ORD.DATA.value('(OrderInfo/@FCOrganizationalLevel)[1]' ,'VARCHAR(100)') AS OrderInfo_FCOrganizationalLevel
,LIC.DATA.value('@LicenseId' ,'VARCHAR(10)' ) AS LicenseId
,LIC.DATA.value('@StartDate' ,'DATE' ) AS StartDate
,LIC.DATA.value('(Capabilities/Capability/@Name)[1]' ,'VARCHAR(10)' ) AS Capability_1
,LIC.DATA.value('(Capabilities/Capability/@Name)[2]' ,'VARCHAR(10)' ) AS Capability_2
FROM @TBLA TA
CROSS APPLY TA.OrderXML.nodes('Order') ORD(DATA)
CROSS APPLY ORD.DATA.nodes('OrderInfo/Licensing/Licenses/License') LIC(DATA);
December 28, 2018 at 4:07 pm
Thank you so much for all the help!
December 29, 2018 at 5:22 am
OlyKLin - Friday, December 28, 2018 4:07 PMThank you so much for all the help!
You are very welcome.
😎
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply