|
The comments still stand - there should be no reason to store the productname in a different table especially as it is user input and free form.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
I have a query that is using FOR XML PATH, when the child elements has values then its giving the Child elements, but when there are no values coming out of query for the Child elements, its finishing the X-path by putting and end tag (\), but I want the all the elements to be created if there are values are not, it doesn't have to repeat the elements as if there are values but at least one occurrence has to be there even if there are values or don't have values.
Below are the example for it.
Case 1 is the file that it is creating with all the values and elements, there are elements that are repeated I am not putting all that xml here to reduce the size, but I want to create all the elements of the xml even if there are values or no values at least one occurrence.
<TB_BILLING_PROVIDER_FILES>
<TB_TRADING_PARTNER>
<Owner>DMH</Owner>
<TP_EIN>9xxxxxxx1</TP_EIN>
<TP_NAME>Alameda</TP_NAME>
<County_Code>01</County_Code>
<TB_BILLING_PROVIDER_FILE>
<TB_SERVICE_FACILITY>
<Service_Facility_NPI>xxxxxxxx7</Service_Facility_NPI>
<County_Owned_Indicator>true</County_Owned_Indicator>
<Service xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Procedure_Code>Hxxxxx</Procedure_Code>
<Modifier1>HE</Modifier1>
<Modifier2>TG</Modifier2>
<Modifier3/>
<Modifier4/>
<From_Date>19811201</From_Date>
<To_Date>20030623</To_Date>
<Revenue_Code/>
<Place_Of_Service/>
<Taxonomy_Code/>
</Service>
</TB_SERVICE_FACILITY>
</TB_BILLING_PROVIDER_FILE>
</TB_TRADING_PARTNER>
</TB_BILLING_PROVIDER_FILES>
The Case 2 xml is as below:
<TB_BILLING_PROVIDER_FILES>
<TB_TRADING_PARTNER>
<Owner>DMH</Owner>
<TP_EIN>xxxxxxxxx</TP_EIN>
<TP_NAME>Alameda</TP_NAME>
<County_Code>01</County_Code>
<TB_BILLING_PROVIDER_FILE/>
</TB_TRADING_PARTNER>
</TB_BILLING_PROVIDER_FILES>
The sql script for the case 1 is:
SELECT
vw_OwnerXML5010.Owner,
vw_OwnerXML5010.Billing_EIN AS TP_EIN,
vw_OwnerXML5010.TP_Name AS TP_NAME,
vw_OwnerXML5010.County_Code AS County_Code,
(
SELECT
(
SELECT
vw_ServiceFacilityXML5010.Service_Facility_NPI AS "Service_Facility_NPI",
vw_ServiceFacilityXML5010.County_Owned_Indicator AS "County_Owned_Indicator",
(
Select
vw_ServicesXML5010.Procedure_Code AS "Procedure_Code",
vw_ServicesXML5010.Modifier1 As "Modifier1",
vw_ServicesXML5010.Modifier2 AS "Modifier2",
vw_ServicesXML5010.Modifier3 AS "Modifier3",
vw_ServicesXML5010.Modifier4 AS "Modifier4",
vw_ServicesXML5010.From_Date AS "From_Date",
vw_ServicesXML5010.To_Date AS "To_Date",
vw_ServicesXML5010.Revenue_Code AS "Revenue_Code",
vw_ServicesXML5010.Place_Of_Service AS "Place_Of_Service",
vw_ServicesXML5010.Taxonomy_Code AS "Taxonomy_Code"
FROM
vw_ServicesXML5010
WHERE
vw_ServiceFacilityXML5010.Service_Facility_NPI = vw_ServicesXML5010.Service_Facility_NPI
and vw_ServiceFacilityXML5010.County_Code = vw_ServicesXML5010.County_Code
and vw_ServiceFacilityXML5010.Billing_EIN = vw_ServicesXML5010.Billing_EIN
-- and vw_ServiceFacilityXML5010.County_Code in ('01', '02')
-- and vw_ServiceFacilityXML5010.Service_Facility_NPI = '1932328580'
FOR XML PATH('Service'), TYPE , ELEMENTS XSINIL
)
FROM
vw_ServiceFacilityXML5010
WHERE
vw_ServiceFacilityXML5010.County_Code = vw_OwnerXML5010.County_Code
and vw_ServiceFacilityXML5010.Billing_EIN = vw_OwnerXML5010.Billing_EIN
-- and vw_ServiceFacilityXML5010.County_Code in ( '01', '02')
-- and vw_ServicesXML5010.Service_Facility_NPI = '1932328580'
FOR XML PATH ('TB_SERVICE_FACILITY'), TYPE
-- ) AS "BILLING_PROVIDERS/TB_BILLING_PROVIDER_FILE/TB_SERVICE_FACILITY"
--) AS "TB_BILLING_PROVIDER_FILE/TB_SERVICE_FACILITY"
--) AS "TB_BILLING_PROVIDER_FILE"
) FOR XML PATH ('TB_BILLING_PROVIDER_FILE'), TYPE
)
FROM
vw_OwnerXML5010
-- where vw_OwnerXML5010.County_Code in ('01', '02')
ORDER BY vw_OwnerXML5010.County_Code
FOR XML PATH ('TB_TRADING_PARTNER'), root('TB_BILLING_PROVIDER_FILES')
The sql script for the case 2 is:
SELECT
vw_OwnerXML5010.Owner,
vw_OwnerXML5010.Billing_EIN AS TP_EIN,
vw_OwnerXML5010.TP_Name AS TP_NAME,
vw_OwnerXML5010.County_Code AS County_Code,
(
SELECT
(
SELECT
vw_ServiceFacilityXML5010.Service_Facility_NPI AS "Service_Facility_NPI",
vw_ServiceFacilityXML5010.County_Owned_Indicator AS "County_Owned_Indicator",
(
Select
vw_ServicesXML5010.Procedure_Code AS "Procedure_Code",
vw_ServicesXML5010.Modifier1 As "Modifier1",
vw_ServicesXML5010.Modifier2 AS "Modifier2",
vw_ServicesXML5010.Modifier3 AS "Modifier3",
vw_ServicesXML5010.Modifier4 AS "Modifier4",
vw_ServicesXML5010.From_Date AS "From_Date",
vw_ServicesXML5010.To_Date AS "To_Date",
vw_ServicesXML5010.Revenue_Code AS "Revenue_Code",
vw_ServicesXML5010.Place_Of_Service AS "Place_Of_Service",
vw_ServicesXML5010.Taxonomy_Code AS "Taxonomy_Code"
FROM
vw_ServicesXML5010
WHERE
vw_ServiceFacilityXML5010.Service_Facility_NPI = vw_ServicesXML5010.Service_Facility_NPI
and vw_ServiceFacilityXML5010.County_Code = vw_ServicesXML5010.County_Code
and vw_ServiceFacilityXML5010.Billing_EIN = vw_ServicesXML5010.Billing_EIN
-- and vw_ServiceFacilityXML5010.County_Code in ('01', '02')
-- and vw_ServiceFacilityXML5010.Service_Facility_NPI = '1932328580'
FOR XML PATH('Service'), TYPE , ELEMENTS XSINIL
)
FROM
vw_ServiceFacilityXML5010
WHERE
vw_ServiceFacilityXML5010.County_Code = vw_OwnerXML5010.County_Code
and vw_ServiceFacilityXML5010.Billing_EIN = vw_OwnerXML5010.Billing_EIN
-- and vw_ServiceFacilityXML5010.County_Code in ( '01', '02')
-- and vw_ServicesXML5010.Service_Facility_NPI = '1932328580'
FOR XML PATH ('TB_SERVICE_FACILITY'), TYPE
-- ) AS "BILLING_PROVIDERS/TB_BILLING_PROVIDER_FILE/TB_SERVICE_FACILITY"
--) AS "TB_BILLING_PROVIDER_FILE/TB_SERVICE_FACILITY"
--) AS "TB_BILLING_PROVIDER_FILE"
) FOR XML PATH ('TB_BILLING_PROVIDER_FILE'), TYPE
)
FROM
dbo.vw_SDMCPhaseTwoParentLevelXML vw_OwnerXML5010
-- where vw_OwnerXML5010.County_Code in ('01', '02')
ORDER BY vw_OwnerXML5010.County_Code
FOR XML PATH ('TB_TRADING_PARTNER'), root('TB_BILLING_PROVIDER_FILES')
I am not understanding how are these two different, I just want to create the complete xml at least with one occurrence of all the elements, can anybody please help me, any help a suggestion a link or code snippet, anything helps. Thanks in advance buddies.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Last tuesday I made a suggestion to you that would probably help you get assistance.
CHill60 wrote: You might get a better response with some sample data and expected results
You've given us the outputs but still haven't provided any sample data ... on the tables (or views) vw_ServicesXML5010, vq_ServiceFacilityXML5010, vw_OwnerXML5010 or dbo.vw_SDMCPhaseTwoParentLevelXML
You have said that you don't understand how the two scripts are different, yet one of them is reading from vw_OwnerXML5010 but the other is reading from dbo.vw_SDMCPhaseTwoParentLevelXML having been given an alias of vw_OwnerXML5010. I suggest you start by comparing the output of the following sql commands
SELECT * FROM vb_OwnerXML5010 and
SELECT * FROM dbo.vw_SDMCPhaseTwoParentLevelXML introducing an appropriate WHERE clause to both.
We can't help you any further without sample data.
|
|
|
|
|
Hi,
What is the equivalent of the following query without the FOR XML PATH, when removed the FOR XML PATH from the query it is giving error, can anybody please help with this regards, thanks in advance buddies.
SELECT
vw_OwnerXML5010.Owner,
vw_OwnerXML5010.Billing_EIN AS TP_EIN,
vw_OwnerXML5010.TP_Name AS TP_NAME,
vw_OwnerXML5010.County_Code AS County_Code,
(
SELECT
(
SELECT
vw_ServiceFacilityXML5010.Service_Facility_NPI AS "Service_Facility_NPI",
vw_ServiceFacilityXML5010.County_Owned_Indicator AS "County_Owned_Indicator",
(
Select
vw_ServicesXML5010.Procedure_Code AS "Procedure_Code",
vw_ServicesXML5010.Modifier1 As "Modifier1",
vw_ServicesXML5010.Modifier2 AS "Modifier2",
vw_ServicesXML5010.Modifier3 AS "Modifier3",
vw_ServicesXML5010.Modifier4 AS "Modifier4",
vw_ServicesXML5010.From_Date AS "From_Date",
vw_ServicesXML5010.To_Date AS "To_Date",
vw_ServicesXML5010.Revenue_Code AS "Revenue_Code",
vw_ServicesXML5010.Place_Of_Service AS "Place_Of_Service",
vw_ServicesXML5010.Taxonomy_Code AS "Taxonomy_Code"
FROM
vw_ServicesXML5010
WHERE
vw_ServiceFacilityXML5010.Service_Facility_NPI = vw_ServicesXML5010.Service_Facility_NPI
and vw_ServiceFacilityXML5010.County_Code = vw_ServicesXML5010.County_Code
and vw_ServiceFacilityXML5010.Billing_EIN = vw_ServicesXML5010.Billing_EIN
-- and vw_ServiceFacilityXML5010.County_Code in ('01', '02')
-- and vw_ServiceFacilityXML5010.Service_Facility_NPI = '1932328580'
FOR XML PATH('Service'), TYPE , ELEMENTS XSINIL
)
FROM
vw_ServiceFacilityXML5010
WHERE
vw_ServiceFacilityXML5010.County_Code = vw_OwnerXML5010.County_Code
and vw_ServiceFacilityXML5010.Billing_EIN = vw_OwnerXML5010.Billing_EIN
-- and vw_ServiceFacilityXML5010.County_Code in ( '01', '02')
-- and vw_ServicesXML5010.Service_Facility_NPI = '1932328580'
FOR XML PATH ('TB_SERVICE_FACILITY'), TYPE
-- ) AS "BILLING_PROVIDERS/TB_BILLING_PROVIDER_FILE/TB_SERVICE_FACILITY"
--) AS "TB_BILLING_PROVIDER_FILE/TB_SERVICE_FACILITY"
--) AS "TB_BILLING_PROVIDER_FILE"
) FOR XML PATH ('TB_BILLING_PROVIDER_FILE'), TYPE
)
FROM
vw_OwnerXML5010
-- where vw_OwnerXML5010.County_Code in ('01', '02')
ORDER BY vw_OwnerXML5010.County_Code
FOR XML PATH ('TB_TRADING_PARTNER'), root('TB_BILLING_PROVIDER_FILES')
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
You might get a better response with some sample data and expected results
|
|
|
|
|
I am having a query on the Production Server, I am getting an xml out of it. For some reason there is a problem in the data that I am getting, some data is missing, but I can't easily compare and see it on the xml file what am I missing and why.
It would be easier for me if I put the same Data into Table then I can easily compare what am I missing. Or is there anyway that I can read the Data from xml file and put it into a table? I am trying but I have to create table before it seems, but I want to just import into Table like we do from excel files.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
-- modified 15-May-18 13:02pm.
|
|
|
|
|
CHill60 wrote: You might get a better response with some sample data and expected results
My comment still stands.
Indian143 wrote: when removed the FOR XML PATH from the query it is giving error
Are you are expecting us to guess what that error might be?
There are plenty of people who would like to help you solve your problem, but you're not giving us much to go on. We do this in our spare time so expecting us to wade through the SQL to try and create some tables to match it so we can see what is being produced so we can work out how to replace the FOR XML and get appropriate results ... would take more time than most of us have available.
See Some guidelines for posting questions in the forums[^]
|
|
|
|
|
I can understand by can you please help me how can I create dummy values for the views vw_ServicesXML5010, vw_ServiceFacilityXML5010, as those views are not fetching any records, the xml is not generating those elements, is it possible to help me in that regards.
For example if I don't have any records for the View vw_ServicesXML5010 for that where condition I want to have empty value for the elements Procedure_Code, Modifier1, Modifier2, Modifier3, Modifier4, From_Date, To_Date, Revenue_Code, Place_Of_Service, Taxonomy_Code
Same thing applies to the View: vw_ServiceFacilityXML5010, if it doesn't have any records then at least one record set with empty values for the Service_Facility_NPI, County_Owned_Indicator.
Can you please help me buddy, thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
I just got a requirement for a web API to an Oracle database. So I fire up an Oracle DAL from 2010 only to find the reference to System.Data.OracleClient are out of date and it is not recommended. Searching for Oracle Connection does not uncover the references required, connection strings.com was no real help.
Can someone advise me on the current references for VS 2015 to connect to Oracle please. I can't add object to the server as one article suggested just a plain read only connection is required.
[edit0]
Looks like this is the latest tool from Oracle Oracle Data Provider for .NET (ODP.NET)[^]. What a PITA,
First I need an account to download, create an account, nope email already has an account, do the forgot password thing. Ok have account not to refind the link to download.
It has to be run as admin if you want to install for all users, you are not given a choice to install for just yourself. Have to close all VS Instances. I'm betting the 2017 version does not work with 2015 and it seems like you can only have 1 version installed. 
As I sit here waiting for help desk to allow me to install a .net driver I really do not like Oracle.
[/edit0]
[edit1] They have a nuget package - I hate nuget [/edit1]
Never underestimate the power of human stupidity
RAH
modified 7-May-18 3:07am.
|
|
|
|
|
In my experience, System.Data.OracleClient should actually work just fine, it was even getting updated with new functionality for VS2015. BUT, it might break if your Oracle DB gets updated.
Otherwise I strongly recommend to get the managed version of the driver.
It doesn't need installation and it's not sensitive to 32 or 64 bit versions, just paste the dll into the bin folder and add a few lines into the app.config.
Example below. You obviously need to correct for your version of the client.
<configSections>
<remove name="oracle.manageddataaccess.client"/>
<section name="oracle.manageddataaccess.client" type="OracleInternal.Common.ODPMSectionHandler, Oracle.ManagedDataAccess, Version=4.121.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342"/>
</configSections>
<system.data>
<DbProviderFactories>
<remove invariant="Oracle.ManagedDataAccess.Client"/>
<add name="ODP.NET, Managed Driver" invariant="Oracle.ManagedDataAccess.Client" description="Oracle Data Provider for .NET, Managed Driver" type="Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342"/>
</DbProviderFactories>
</system.data>
<oracle.manageddataaccess.client>
<version number="*">
<dataSources>
<dataSource alias="ORCL" descriptor="
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = my.address.net)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)
"/>
</dataSources>
</version>
</oracle.manageddataaccess.client>
<connectionStrings>
<add name="IDB" connectionString="user id=MyUser;data source=orcl;password=MyPassword;Enlist=false" providerName="Oracle.ManagedDataAccess.Client" />
</connectionStrings>configSections NEEDS to be FIRST in the configfile.
|
|
|
|
|
Jörgen Andersson wrote: System.Data.OracleClient
Has been deprecated in favour of Oracles ODP, I chased down a connection string that does not need tsnames.ora that works a treat.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: Has been deprecated in favour of Oracles ODP
For having been deprecated for so many years (2009), it's getting an awful lot of updates. 
Mycroft Holmes wrote: I chased down a connection string that does not need tsnames.ora that works a treat.
The content of the tnsnames.ora can be put in so many different places...
Happy it works for you. Configuration of Oracle is admittedly a PITA.
Me, I'm swearing over the locks in SQL Server and wishing myself back to the stability and performance of Oracle.
|
|
|
|
|
Hello,
I have two SQL 2016 Always On nodes - (VMware Virtual Machines). Each node has 250GB RAM, 46 vCPU @2.5GHz. Each of the MS SQL VM is dedicated to 1 ESXi Host so there is no resource contention.
The application that would connect to the Database is expected to be: 70% WRITE and 30% READ.
I need advise on the best way to:
(1.) Configure MS SQL nodes to use the 250GB RAM, 46 vCPU efficiently and optimally.
(2.) Perform Write and Read as fast as possible using all the hardware resources.
(3.) I have configured Always On Read Routing, how can I test it?
Thanks.
|
|
|
|
|
Please don't repost the same question - either here or in the other forums. Be patient, your message was waiting for a human to confirm it wasn't spam
|
|
|
|
|
Member 13806085 wrote: Perform Write and Read as fast as possible using all the hardware resources.
"As fast as possible" will take some experimenting and reading, will depend on your data-structure, indexes, triggers, and a bunch more - a bit much for a single forum-post.
Member 13806085 wrote: I have configured Always On Read Routing, how can I test it?
Check if the routing-URL is invoked and run a SQL-trace.
Configure Read-Only Routing for an Availability Group (SQL Server) | Microsoft Docs[^]
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
Hello,
I have two SQL 2016 Always On nodes - (VMware Virtual Machines). Each node has 250GB RAM, 46 vCPU @2.5GHz. Each of the MS SQL VM is dedicated to 1 ESXi Host so there is no resource contention.
The application that would connect to the Database is expected to be: 70% WRITE and 30% READ.
I need advise on the best way to:
(1.) Configure MS SQL nodes to use the 250GB RAM, 46 vCPU efficiently and optimally.
(2.) Perform Write and Read as fast as possible using all the hardware resources.
(3.) I have configured Always On Read Routing, how can I test it?
Thanks.
|
|
|
|
|
Hi,
I am writing an SSIS Package, want to stop all the remaining tasks if I get Dts.TaskResult as failure (I am checking if file exists in Script task), I am trying different options like assigning false value to another variable etc, so far no success, can somebody suggest me the best way to stop execution of remaining tasks if a Script task gets failure result, any idea would be greatly helpful. thanks in advance friends.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Hi friends,
Its easy, create a Boolean or some other type variable, assign that value accordingly in the script task, then after the script task, you can have more than one Precedence Constraint Editor, in that, take the Evaluation Operation as either expression or expression and Constraint etc according to your needs then set the expression with your variable that you have assigned in the script task, now after the script task it will either continue with next steps or it will go to the task you have assigned after the failure constraint.
This is all it is, thank you SSIS and thanks for all the friends who tried to help or support me.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
-- modified 3-May-18 14:48pm.
|
|
|
|
|
Hi,
I need to insert Dynamic SQL Values into Temp table, but I am able to insert values into Global Temp table but I don't know how to insert Global Temp table values into Temp table back again. Any help would be greatly helpful. Any sort of implementation to insert Dynamic SQL values into Temp table is fine, directly or indirectly is fine.
Here is how I am able to insert the Dynamic SQL Values into Global temp table, but not able to do the same for the Temp table, even if I can insert from Global Temp table to Temp table is also fine. Any help would be greatly helpful - thanks in advance buddies.
DECLARE @sqlCommand varchar(max) = 'SELECT TABLE_NAME INTO ##TableNames FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='''+ @schema + ''''
EXEC (@sqlCommand)
EXEC ('SELECT * FROM [##TableNames] ')
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
You have to create the temp table before executing the dynamic SQL:
CREATE TABLE #TableNames (TABLE_NAME sysname NOT NULL);
DECLARE @sqlCommand nvarchar(2000) = N'INSERT INTO #TableNames (TABLE_NAME) SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = @schema';
EXEC sp_executesql @sqlCommand, N'@schema nvarchar(128)', @schema = @schema;
SELECT * FROM #TableNames;
DROP TABLE #TableNames;
You also need to avoid string concatenation, otherwise you'll introduce a SQL Injection[^] vulnerability.
sp_executesql (Transact-SQL) | Microsoft Docs[^]
But in this example, you don't need dynamic SQL at all.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thank you very much that did it great, its a great help.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
Hi guys, need help again.. newbie here just learning mysql. I have a timesheet table that have a few columns. ie Date, InvNum, Fieldhrs, UserID, ShopHrs.
Everyday employees enter their data into database via web page which works great.
My problem is this, trying to write a query that checks if every user has entered data for a certain date.. i've tried the following query, but just returns blank not userid ?
SELECT UserID FROM timesheet
WHERE "2018-04-23" NOT IN
(
SELECT Date
FROM timesheet
)
Any help would be appreciated if someone could point me in right direction.. ie a real newbie.
|
|
|
|
|
You need a reference for all active users.
Assuming you have a Users table you should try something similar to this:
SELECT u.UserID
FROM Users u
LEFT JOIN Timesheet t
ON t.UserID = u.UserID
AND t.Date = "2018-04-23"
WHERE t.UserID IS NULL
AND u.IsActive = 1The existence of IsActive is just an assumption on my part. Add or change conditions as needed.
There are other solutions, but this is the probably the best way to do it.
|
|
|
|
|
Thanks. Very helpful. Got it working following your suggestion. Much appreciated.
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
